Conditional IF ELSE

[kmpmba]

Hi, Need formula to calculate commission based on sales figure, please, can anyone assist, many thanks

 

[Michael M]

Maybe this

=IF(F2<10000,0,IF(F2<20000,1450,IF(F2<30000,2300,IF(F2<40000,3700,5200))))

 

[kmpmba]

thanks but no, once the 1st condition is satisfied it stops, hence the result I see f0r 40000 is 1450 (as 40000 is greater than 10000 it returns corresponding value and does not look further

 

[Michael M]

well based on the data you supplied in"actual Sales" column, all of them should be zero, as none exceed the minimum 10000 sales to get a commision

 

[kmpmba]

My Bad, corrected now

 

[Michael M]

=IF(F2<10000,0,IF(F2<20000,1450,IF(F2<30000,2300,IF(F2<40000,3700,4000))))

 

[jtakw]

Hi,

thanks but no, once the 1st condition is satisfied it stops, hence the result I see f0r 40000 is 1450 (as 40000 is greater than 10000 it returns corresponding value and does not look further

I don't understand the part in red above.

But is this what you mean?

Change the 9999 to 0 in B2

Book3.xlsx
	A	B	C	D	E	F	G
1
2		0	0			23495	2300
3		10000	1450			12970	1450
4		20000	2300			98700	5200
5		30000	3700			45000	5200
6		40000	5200			8899	0
Sheet951

Cell Formulas
Range	Formula
G2:G6	G2	=LOOKUP(F2,B$2:B$6,C$2:C$6)