Formulas

[ad335i]

I am tracking a promotion for a call center. i am trying to setup a formula in excel to help. what i need .
for every sale >=2 and <=4 is worth $5 and and say >=5 is worth $10

 

[yogeshmaney]

=if(Sales<2,0,if(Sales<=4,$5,$10)

 

[Greg Truby]

Nothing wrong with Yogesh's, this is just an alternative:

=LOOKUP(G1,{0,2,4.00000001},{0,5,10})

Do note that in both formulae, 4.01 would return a 10. You did not address values between 4 and 5 (if they're possible).

 

[ad335i]

thanks that is great. now how do i get it to multeply the value from 2 to 4 by $5 and anything above the 4th by $10

 

[ad335i]

for a total payout

 

[yogeshmaney]

Assuming you want to multiply Sales by $5 or $10

=if(Sales<2,0,if(Sales<=4,$5*Sales,$10*Sales)

 

[yogeshmaney]

I request, kindly provide complete data please.

 

[ad335i]

how would you only multiply sales between only 2 and 4 for the fist part and any sale above the 4th by $10 and add those two together to get the final valule.

for example a rep sold 6 products
for sale 2, 3 and 4 they wil get paid $5 each for a total of $15
and sale 5 and 6 they will get paid $10 each for a total of $20
and it will give you a total payout of $35

 

[yogeshmaney]

IF(Sales<2,0,IF(Sales<=4,5*Sales,SUM(5*3,10*(Sales-4))))

 

[ad335i]

thank you that is perfect