Multiple tiers, incremental percentages

Hi Dougie12.,

Try using below formula:-

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

@ DILIPandey
I think you need to divide your solution by 100 or change your percentage multipliers.
Check your result for values less than £35,000

@ Dougie12
To avoid nested IF()s I would build a lookup table such as in this example, then use LOOKUP().
This might also prove a bit more versatile if you need to change band widths or percentages

See the attached

Thanks Marcol for your eye to detail.

Updated the less than 35001 criteria; no need to divide the result by 100, see the attachment.

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

I agree with Marcol about dividing by 100, The question says 0.35% not 35%. 0.35% of 35000 is 122.5

There's a good method for this sort of problem suggested here

If you use that then a possible formula will be

=SUMPRODUCT((A1>{0,35000,90000,150000})+0,A1-{0,35000,90000,150000},{0.35,-0.05,-0.15,-0.05}%)

or using Marcol's table setup this will translate to this formula in B2

=SUMPRODUCT((A2>B7:B10)+0,A2-B7:B10,D7:D10-D6:D9)

[Note D6 should remain blank or be zero]

....or another way to give the same result.....

=IF(A1="","",A1*0.1%+MIN(A1,150000)*0.05%+MIN(A1,90000)*0.15%+MIN(A1,35000)*0.05%)

Oops... you are correct daddylonglegs.... sorry Marcol.. you were correct. I forgot to consider that 0.35 has a % sign attached and I thought it is 35% (0.35).

Thanks to both of you.

Regards,
DILIPandey

<click on below 'star' if this helps>