Help with nested IF function

[NZahenaiko]

Hi everyone

I'm trying to complete a spreadsheet for our group benefits.

Our one benefit is based on a graded schedule(66.67% of the first 6000 monthly earnings, 55% of the next 3000 monthlyearnings, then 40% of the remaining earnings up to a maximum benefit of 8000per month.

I have a column showing the month earnings andI thought this would work, but it doesn’t:

IF((G2<=6000,(G2*0.6667),IF(AND(G2>6000,G2<=9000),(6000*0.6667)+(G2-6000)*0.55)))

 

[Eric W]

Welcome to the forum.

Maybe:

=MIN(8000,MIN(G2,6000)*66.67%+MEDIAN(0,G2-6000,3000)*55%+MAX(0,G2-9000)*40%)

 

[sheetspread]

This works too:

Excel 2010
	A	B
1	17000	8850.2
Sheet1

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(A1>{0;6000;9000;17000}),(A1-{0;6000;9000;17000}),{0.6667;-0.1167;-0.15;-0.4})