If formula- I need a formula that has many functions

[John]

I need a formula that has many functions. The formula will be in cell g2. I
want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is
less than 50,000 then the number in a1 should show in g2. If the number in e2
is greater than 50,000 then I need a formula that takes the number in e2
subtracts the number in d1, then divides that number by 1,000. Then that
number is multiplied the number in c1 and then add to this number the number
in a1. Basically the number in a1 is 166.35 and does not change. The number
in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not
change. The number in e2 is the only one that changes and this is the usage
number. The customer receives the 50,000 gallons for 166.35 and for every
1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is
blank or 0 then I need the number in g2 to be 0.
If this is too confusing please let me know and I will try to simplify it.
Thanks for any help.

--
John

[davesexcel]

well yeh,
simplify it,
this looks like something that has to be done in VBA

[andy62]

try pasting this in cell G2:

=IF(E2=0,0,IF(E2<50000,$A$1,$A$1+$C$1*(E2-$D$1)/1000))

The $ characters will preserve the references to the fixed cells (A1, C1,
and D1) if you copy the formula down to other rows below G2.

Hope that helps.

"John" wrote:

> I need a formula that has many functions. The formula will be in cell g2. I
> want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is
> less than 50,000 then the number in a1 should show in g2. If the number in e2
> is greater than 50,000 then I need a formula that takes the number in e2
> subtracts the number in d1, then divides that number by 1,000. Then that
> number is multiplied the number in c1 and then add to this number the number
> in a1. Basically the number in a1 is 166.35 and does not change. The number
> in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not
> change. The number in e2 is the only one that changes and this is the usage
> number. The customer receives the 50,000 gallons for 166.35 and for every
> 1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is
> blank or 0 then I need the number in g2 to be 0.
> If this is too confusing please let me know and I will try to simplify it.
> Thanks for any help.
>
> --
> John

[andy62]

try pasting this in cell G2:

=IF(E2=0,0,IF(E2<50000,$A$1,$A$1+$C$1*(E2-$D$1)/1000))

The $ characters will preserve the references to the fixed cells (A1, C1,
and D1) if you copy the formula down to other rows below G2.

Hope that helps.


"John" wrote:

> I need a formula that has many functions. The formula will be in cell g2. I
> want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is
> less than 50,000 then the number in a1 should show in g2. If the number in e2
> is greater than 50,000 then I need a formula that takes the number in e2
> subtracts the number in d1, then divides that number by 1,000. Then that
> number is multiplied the number in c1 and then add to this number the number
> in a1. Basically the number in a1 is 166.35 and does not change. The number
> in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not
> change. The number in e2 is the only one that changes and this is the usage
> number. The customer receives the 50,000 gallons for 166.35 and for every
> 1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is
> blank or 0 then I need the number in g2 to be 0.
> If this is too confusing please let me know and I will try to simplify it.
> Thanks for any help.
>
> --
> John

[John]

The formula worked perfectly! Thanks for your help.

--
John


"andy62" wrote:

> try pasting this in cell G2:
>
> =IF(E2=0,0,IF(E2<50000,$A$1,$A$1+$C$1*(E2-$D$1)/1000))
>
> The $ characters will preserve the references to the fixed cells (A1, C1,
> and D1) if you copy the formula down to other rows below G2.
>
> Hope that helps.
>
> "John" wrote:
>
> > I need a formula that has many functions. The formula will be in cell g2. I
> > want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is
> > less than 50,000 then the number in a1 should show in g2. If the number in e2
> > is greater than 50,000 then I need a formula that takes the number in e2
> > subtracts the number in d1, then divides that number by 1,000. Then that
> > number is multiplied the number in c1 and then add to this number the number
> > in a1. Basically the number in a1 is 166.35 and does not change. The number
> > in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not
> > change. The number in e2 is the only one that changes and this is the usage
> > number. The customer receives the 50,000 gallons for 166.35 and for every
> > 1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is
> > blank or 0 then I need the number in g2 to be 0.
> > If this is too confusing please let me know and I will try to simplify it.
> > Thanks for any help.
> >
> > --
> > John