Choose a formula from a cell and calculate it

Hello All!

This is my first post. Although i am not an expert, i use excel on a daily basis and i ussually am able the solve or find on the web the solutions for the "challenges" i have, but this time i am really stucked. I think that what i want is possible, but not he way i am thinking it.

So let's see if the comunity can help me.

The Worksheet represents the sales results and comissions of several employes.

Each Employe might have an agreed condition of comission, with diferent % by range of sales.

For Exemple

Employe (A) has

0-150 = 35%
150-200 = 40%
> 200 = 45%

Employe (B) has

0-100 = 32%
100-200 = 35%
200-300 = 40%
>300 = 45%

This is just a very simplier example, to even know the sales of each there is a bunch of sheets and data that calculates the final % to be applied.

To Calculate the comissions, when they we're few, i just made get the % with some if's, something like that

"=IF(P13="EmployeA";IF(T13<65;0,375;IF(T13<85;0,4;IF(T13<105;0,425;IF(T13<125;0,45;IF(T13>125;0,5)))));IF(p13="employeb";(T13<50;0,375;IF(T13<60;0,4;IF(T13<70;0,425;IF(T13<90;0,45;IF(T13>90;0,5)"

But now there are too many and i am making some rude mistakes on the formules so my idea as to have a sheet where i put

A | B

Employe A | IF(T13<85;0,4;IF(T13<105;0,425;IF(T13<125;0,45;IF(T13>125;0,5))))
Employe B | IF(T13<85;0,44;IF(T13<105;0,43;IF(T13<125;0,49;IF(T13>125;0,59))))

(I do not insert the "=" so it does not try to calcule)

Then on the calculation **** i would make a Vlookup to get the percentage for the comission of that Employe on this table above and it calculates.


I can of course get the formula by Concatenate "=" and vlookup on the calculation sheet and i get the correct complete formula, but... it shows text, does not calcule the result.

Is there a way for me to setup this conditions ranges per employe and then get the % from the cell that has those ranges to be applied?

I hope i made clear

Hi. Thanks for the heads up. i will prepare it to attach, current file has to much info. thanks

Hi
I uploaded a sample for you based on your description, hope I understood you correct.

In sheet1 you need maintain all your manual data per employee
In sheet 2 use this:

=LOOKUP(B1,FILTER(Sheet1!$B$1:$B$99,Sheet1!$A$1:$A$99=Sheet2!A1),FILTER(Sheet1!$C$1:$C$99,Sheet1!$A$1:$A$99=Sheet2!A1))

I think i was able to simplify it enough

i removed most employes and the other sheets that are not relevant

In Yellow there are the relevante collumns for the and blue the expected result

Now lets see if i can attached it well to the message :D

My Formula gives me the formula, the text.

I want it to give me the result, that gives me make the calculation of the formula and i get the % applied

the collum that is not giving me the info is the yellow - "R"

the collumn "u" is giving the result but i made it employe by employe

Hi
I applied my formula on your sheet, see if that suits your expected results
In rates tab maintain all employees names , ranges, and corresponding rates

In 'ResSemanas' R2 :
=LOOKUP(Q2,FILTER(RATES!$B$1:$B$99,RATES!$A$1:$A$99=ResSemanas!A2),FILTER(RATES!$C$1:$C$99,RATES!$A$1:$A$99=ResSemanas!A2))

Hello!

Thank you very Much. I see that the solutions works and i can apply this to my problema!!!

I still did not understood fully the "filter" formula, but probably it's because it's already late here at my side of the country. i will review it tomorrow

Really Really Thanks

You're welcome nspinheiro from beautiful Lisbon