IF AND FORMULA REQUEST

[Tonto]

I need a formula to deal with the following problem.

If ORDERS are 4 or more AND MONTHLY CUMULATIVE is greater than or equal
to 750000 then everything up to 749999 is at .075% bonus and everything
greater than or equal to 750000 is at .01% bonus. If ORDERS are less
than 4 then no bonus. If amount less then 750000 but orders are 4 or
more then bonus is at .075%.

I have tried
=IF(AND(E4>=$B$14,G4>=$B$20),((G4-$B$20)*$B$17)+(F4-(G4-$B$20))*($B$16),IF(E4<B14,0,F4*$B$16))
in cell H4 but this fails


E2 F2 G2
H2
Orders Invoice_Value May_Cum Commission
4 150000 850000 ?


Thanks in anticipation


John

 

[guilbj2]

I can help with this, but please provide details of where all of thes
values are located. Your formula has specific cell references, bu
rather than try to reverse engineer this, can you follow up with a lis
of what columns/rows contain which data values (orders, cumulative tota
for the month etc.

 

[Bob Phillips]

I don't understand what all the other cells are, B14, B17, B20 etc., but
assuming that
E4 is the number of orders
F4 is the cumulative sales
H1 is the threshold (750000 here)
H2 is the first percentage (0.075%)
H3 is the second percentage (0,01%),

then

=IF(E4<4,0,MIN(F4,H1)*H2+MAX(0,F4-H1)*H3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Tonto]

I am confused Bob but mainly by my own sillyness!

The fixed data is -
Min Orders 4 B14

Basic Com 0.0075 B16
Upper Com 0.01 B17

UpInvoice 749999 B19
OverInvoice 750000 B20


Does your formula still work?


Thanks


John

 

[Tonto]

Values

E4 = Orders
F4 = Months Invoice Value
G4 = Cumulative Invoice Value
H4 = Commision (where the formula lives!)

B14=Min Orders (4)
B16= Basic Commission (0.075%)
B17= Upper Commision (0.01%)
B20 = Cumulative Invoice threshold for upper commision.

Thanks

John

 

[Bob Phillips]

Yes, just adjust the cells I mentioned for those you mention.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)