If AND THEN FORMULAS

[Crudepuddle]

Hi,

I am attempting to create a formula to work out commission paid based on a series of circumstances based on %age level of achievement:

TH 3,500
TA 10,000
GSA 20,000
WOD 2,000
CP

0 to TH 0%
TH to (TA*60%) 10%
(TA*60%) to TA 15%
TA + 20%

Am trying to create a formula that includes all the below:

If GSA <= TH Then 0
IF GSA >TH BUT <= TA*60% Then WOD*10%
IF GSA > TH*60% BUT <= TA Then WOD*15%
IF GSA > TA Then WOD*20%

[JohnTopley]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[Crudepuddle]

Example attached

[JohnTopley]

Try

=IF(G4<=G2,0,IF(G4>G3,G5*0.2,IF(AND(G4>G2,G4<G3*0.6),G5*0.1,IF(AND(G4>G2*0.6,G4<=G3),G5*0.15))))