if function in msexcel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

in coloumn c i put all sales value & i hv slab of =50000, >50000<70000 &
70000 & commission % is 2%,3%,4% for above slab.
Click to expand...
kindly tell me how to use if function to get commission on the base of
achieved sale which is in coloumn "C"
 
Assuming sales in C2 down,

Try in D2:
=VLOOKUP(C2,{1,0.02;50001,0.03;70000,0.04},2,TRUE)
Format D2 as percentage and copy down
 
Take a look here:

http://www.mcgimpsey.com/excel/variablerate.html

Assuming that the first 50000 returns a commission of 2%, the next 20000
returns 3% and anything above that returns 4%, you could use

=SUMPRODUCT(--(SUM(C:C)>{0,50000,70000}),(SUM(C:C)-{0,50000,70000}),
{0.02,0.01,0.01})


See the link above for more flexible solutions...
 
Back
Top