To many arguments for function

  • Thread starter Thread starter scottish_surfer
  • Start date Start date
S

scottish_surfer

Hello, calling all excel gurus out there I need your help quick please
please. I have a spreadsheet which I want to perform the followin
function on an individual cell that has already been totaled "<tota
value". The commision needs to be calculated in different commisio
bands depending on the value of "<total vaule>". If say the <tota
value> cell is 7,000 pounds then the commision would be 5% of 4000
200 + 12.5% of the remainder (3000). I have included the band belo
but i believe excel has a limitation with the amount of arguments in a
if statement.

Bands
~~~~~
0 - 4000 calculated at 5%
4000 - 8000 calculated at 12.5%
8000 - 20000 calculated at 20%
over 20000 calculate at 25 %

what i tried
=IF(G27<=4000,(G27*5%),IF(G27<=8000,(G27-4000)*(12.5%)+200),IF(G27<=20000,(G27-12000)*(20%)+700))

But this does not work, cant seem to get my head round how I can d
this formula based on different conditions....please help

Thanks :confused
 
Hi,

Try

=IF(G27<=4000,(G27*5%),IF(G27<=8000,((G27-4000)*12.5%)+200,IF(G27<=20000,((G27-8000)*20%)+700,((G27-20000)*25%)+3100)))

Regards

Govind.
 
An alternative with no IFs

=MIN(N(G27),4000)*5%+MIN(MAX(G27-4000,0),4000)*12.5%+MIN(MAX(G27-8000,0),120
00)*20%+MIN(MAX(G27-20000,0),G27)*25%
 
Back
Top