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

--
HTH

Bob Phillips

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

"Tonto" wrote in message
oups.com...
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



Bob Phillips wrote:

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" wrote in message
ups.com...
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(E
4<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