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_*** Commission
4 150000 850000 ?


Thanks in anticipation


John

[guilbj2]

I can help with this, but please provide details of where all of these values are located. Your formula has specific cell references, but rather than try to reverse engineer this, can you follow up with a list of what columns/rows contain which data values (orders, cumulative total 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" <[email protected]> wrote in message
news:[email protected]...
> 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_*** Commission
> 4 150000 850000 ?
>
>
> Thanks in anticipation
>
>
> John
>

[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



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" <[email protected]> wrote in message
> news:[email protected]...
> > 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_*** Commission
> > 4 150000 850000 ?
> >
> >
> > Thanks in anticipation
> >
> >
> > 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


Tonto wrote:

> 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_*** Commission
> 4 150000 850000 ?
>
>
> Thanks in anticipation
>
>
> 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)

"Tonto" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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_*** Commission
> > > 4 150000 850000 ?
> > >
> > >
> > > Thanks in anticipation
> > >
> > >
> > > John
> > >
>