Help on SUMIF

[timotee]

working cells A31,B31,B32,B33. If B31>A31,then B32 is (A31-B31)*8. If
B31<A31, then B32 is (9999-A31)+B31*8.

I'm tryin to clock a electric meter that rolls around every 9999 units.
After 9999, it starts on 1 again. I have other cells to covert data to money,
but listing that right now would make things too complicated.

My formula for B32 is =IF(B31>A31,"SUM(B31-A31)*8","=SUM(9999-A31)+B31*8").

I can't get my orginal thought to pan out. On cell B33, I multiply B32 value
by .01057 to convert to dollars.

If you go from 9999 to 50, the difference is 9940. The real billing units
are 51, because the meter ends at 10000.

Any help greatly appreciated.

[Gary''s Student]

Instead of
(9999-A31)+B31*8
try
((9999-A31)+B31*)8
--
Gary's Student


"timotee" wrote:

> working cells A31,B31,B32,B33. If B31>A31,then B32 is (A31-B31)*8. If
> B31<A31, then B32 is (9999-A31)+B31*8.
>
> I'm tryin to clock a electric meter that rolls around every 9999 units.
> After 9999, it starts on 1 again. I have other cells to covert data to money,
> but listing that right now would make things too complicated.
>
> My formula for B32 is =IF(B31>A31,"SUM(B31-A31)*8","=SUM(9999-A31)+B31*8").
>
> I can't get my orginal thought to pan out. On cell B33, I multiply B32 value
> by .01057 to convert to dollars.
>
> If you go from 9999 to 50, the difference is 9940. The real billing units
> are 51, because the meter ends at 10000.
>
> Any help greatly appreciated.
>

[Roger Govier]

Hi

You were almost there. You don't need quotes around your formulae, but
you do need an extra set of brackets.
Try
=IF(B31>A31,(B31-A31)*8,((10000-A31)+B31)*8)
I think you need to use 10,000 and not 9999 as I am sure you would get
charged for the unit used from 9999 to 10000.

--
Regards

Roger Govier


"timotee" <[email protected]> wrote in message
news:[email protected]...
> working cells A31,B31,B32,B33. If B31>A31,then B32 is (A31-B31)*8. If
> B31<A31, then B32 is (9999-A31)+B31*8.
>
> I'm tryin to clock a electric meter that rolls around every 9999
> units.
> After 9999, it starts on 1 again. I have other cells to covert data to
> money,
> but listing that right now would make things too complicated.
>
> My formula for B32 is
> =IF(B31>A31,"SUM(B31-A31)*8","=SUM(9999-A31)+B31*8").
>
> I can't get my orginal thought to pan out. On cell B33, I multiply B32
> value
> by .01057 to convert to dollars.
>
> If you go from 9999 to 50, the difference is 9940. The real billing
> units
> are 51, because the meter ends at 10000.
>
> Any help greatly appreciated.

[daddylonglegs]

I think this would be easier...

=(B31-A31+(A31>B31)*10000)*8

If you also want to include your dollars calculation

=(B31-A31+(A31>B31)*10000)*8*0.01057