Multiple IFs formula !!!!

[Bahaa]

I have to use a IF formula to calculate commision payments at a different % categories.

The target fixed cell is B4 100,000 USD
if he achieve 100% of his target , he will be eligible to 30% commisison
if he achieve between 75% & 99% of his target , he will be eligible to 20% commisison
if he achieve between 50% & 74% of his target , he will be eligible to 10% commisison
below 50% commission is Zero

Thank you

 

[etaf]

how about
using IF - but you could use tables

is that 20% of what he has achieved or 20% of the 100,000

=IF( B4>=100000,100000*0.3,if(b4>=(0.75*100000),100000*0.2,if(b4>=(0.5*100000),100000*0.1,0)))

 

[AlanY]

try

=IF(B4>=100000, B4*IF(B4>=100000, 0.3, IF(B4>=75000, 0.2, IF(B4>=50000, 0.1, 0))), IF(B4>=75000, B4*0.2, IF(B4>=50000, B4*0.1, 0)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Alex Blakenburg]

I always use table for that sort of thing (as @etaf also suggested)

Book1
	A	B	C	D	E	F	G	H
1
2
3
4	Target	100,000
5					Lookup Achieved % to get Commission Rate
6
7	Employee	Sales	% Commission		From	To (Optional)	Commision Rate
8	John	30,000	0%		0%	49%	0%
9	Peter	50,000	10%		50%	74%	10%
10	Mary	70,000	10%		75%	99%	20%
11	Alfred	90,000	20%		100%		30%
12	Zoe	110,000	30%
13
Sheet1

Cell Formulas
Range	Formula
C8:C12	C8	=VLOOKUP(ROUND(B8/$B$4,2),$E$8:$G$11,3,TRUE)