Thread: commission formula query

I am trying to work out the formula for the following:

If C26 is between 15 and 19, then I'd like to add 100 to the total in H26.
If C26 is 20 or above, then I'd like to add 150 to H26.
If C26, D26, E26, F26 are all above 15 then I'd like to add an extra 100 to H26.

Any help would be greatly appreciated!

Thanks.

Add a commission lin into row 27

C27 - =IF(C26<15,0,(IF(C26<20,100,150)))
D27 - =IF(D26<15,0,(IF(D26<20,100,150)))
E27 - =IF(E26<15,0,(IF(E26<20,100,150)))
F27 - =IF(F26<15,0,(IF(F26<20,100,150)))
G27 - =IF(((IF(C26>14,1,0))+(IF(D26>14,1,0))+(IF(E26>14,1,0))+(IF(F26>14,1,0)))=4,150,0)

Thanks,
Jon

Sorry

G27 needs to be -

=IF(((IF(C26>14,1,0))+(IF(D26>14,1,0))+(IF(E26>14,1,0))+(IF(F26>14,1,0)))=4,100,0)

Is there any way around this without having to add an extra line below?
It's only because I have to use this formula for 8 rows of different people and it might get a bit messy if I have to add a line below each person...

Thanks!

commission screenshot.jpg

here is a screen shot of my spreadsheet - does that help?

No, I entered the formula and tried entering my figures, however it just displayed #value!

Yes, I pressed those keys together after pasting the formula and still no joy

In Cell I27 put the following formula in:

=(IF(C26<15,0,(IF(C26<20,100,150))))+(IF(D26<15,0,(IF(D26<20,100,150))))+(IF(E26<15,0,(IF(E26<20,100 ,150))))+(IF(F26<15,0,(IF(F26<20,100,150))))+(IF(G26<15,0,(IF(G26<20,100,150))))+J26

in J26 put the formula =IF(((IF(C26>14,1,0))+(IF(D26>14,1,0))+(IF(E26>14,1,0))+(IF(F26>14,1,0)))=4,100,0) and make the text white so people cannot see it....

I know the formula is large but it will get the desired effect!

Put formula into I26 even....