Is there a formula I can use to subtract meter readings to show usages. For
Example:
A B C D E
1 Date Meter1 Usage1 Meter2 Usage2
2 1/1/01 25 25 10 10
3 1/2/01 30 5 26 16
4 1/3/01 30 4
5 1/4/01 36 ?
6 1/5/01 39 3 40 ?

In the crude example above the meter readings are manually put in where the
usage formulas were IF formulas. However I am trying to get away from using
IF formulas or nested IF formulas since I have no idea how many blank entries
there will be in a row nor do I know when there will be blank entries?

Does anyone know of a formula (not a macro) that can accomplish this
perplexing situation?

You have to put the first values in B2 and D2; but then

Put this in C3 and copy down... =IF(B3="","",B3-SUM(C$2:C2))

Put this in D3 and copy down... =IF(D3="","",D3-SUM(E$2:E2))

Rick


"ttam78ouch" wrote in message
...
Is there a formula I can use to subtract meter readings to show usages.
For
Example:
A B C D E
1 Date Meter1 Usage1 Meter2 Usage2
2 1/1/01 25 25 10 10
3 1/2/01 30 5 26 16
4 1/3/01 30 4
5 1/4/01 36 ?
6 1/5/01 39 3 40 ?

In the crude example above the meter readings are manually put in where
the
usage formulas were IF formulas. However I am trying to get away from
using
IF formulas or nested IF formulas since I have no idea how many blank
entries
there will be in a row nor do I know when there will be blank entries?

Does anyone know of a formula (not a macro) that can accomplish this
perplexing situation?

Try these:

Enter this formula in C2 and copy down as needed:

=IF(B2="","",IF(COUNT(B$2:B2)=1,B2,B2-LOOKUP(1E100,B$1:B1)))

Enter this formula in E2 and copy down as needed:

=IF(D2="","",IF(COUNT(D$2:D2)=1,D2,D2-LOOKUP(1E100,D$1:D1)))

--
Biff
Microsoft Excel MVP


"ttam78ouch" wrote in message
...
Is there a formula I can use to subtract meter readings to show usages.
For
Example:
A B C D E
1 Date Meter1 Usage1 Meter2 Usage2
2 1/1/01 25 25 10 10
3 1/2/01 30 5 26 16
4 1/3/01 30 4
5 1/4/01 36 ?
6 1/5/01 39 3 40 ?

In the crude example above the meter readings are manually put in where
the
usage formulas were IF formulas. However I am trying to get away from
using
IF formulas or nested IF formulas since I have no idea how many blank
entries
there will be in a row nor do I know when there will be blank entries?

Does anyone know of a formula (not a macro) that can accomplish this
perplexing situation?

Thank you, I figured I was overlooking the obvious...works to perfection!

"T. Valko" wrote:

Try these:

Enter this formula in C2 and copy down as needed:

=IF(B2="","",IF(COUNT(B$2:B2)=1,B2,B2-LOOKUP(1E100,B$1:B1)))

Enter this formula in E2 and copy down as needed:

=IF(D2="","",IF(COUNT(D$2:D2)=1,D2,D2-LOOKUP(1E100,D$1:D1)))

--
Biff
Microsoft Excel MVP


"ttam78ouch" wrote in message
...
Is there a formula I can use to subtract meter readings to show usages.
For
Example:
A B C D E
1 Date Meter1 Usage1 Meter2 Usage2
2 1/1/01 25 25 10 10
3 1/2/01 30 5 26 16
4 1/3/01 30 4
5 1/4/01 36 ?
6 1/5/01 39 3 40 ?

In the crude example above the meter readings are manually put in where
the
usage formulas were IF formulas. However I am trying to get away from
using
IF formulas or nested IF formulas since I have no idea how many blank
entries
there will be in a row nor do I know when there will be blank entries?

Does anyone know of a formula (not a macro) that can accomplish this
perplexing situation?

Thank you . . . worked great!

"Rick Rothstein (MVP - VB)" wrote:

You have to put the first values in B2 and D2; but then

Put this in C3 and copy down... =IF(B3="","",B3-SUM(C$2:C2))

Put this in D3 and copy down... =IF(D3="","",D3-SUM(E$2:E2))

Rick


"ttam78ouch" wrote in message
...
Is there a formula I can use to subtract meter readings to show usages.
For
Example:
A B C D E
1 Date Meter1 Usage1 Meter2 Usage2
2 1/1/01 25 25 10 10
3 1/2/01 30 5 26 16
4 1/3/01 30 4
5 1/4/01 36 ?
6 1/5/01 39 3 40 ?

In the crude example above the meter readings are manually put in where
the
usage formulas were IF formulas. However I am trying to get away from
using
IF formulas or nested IF formulas since I have no idea how many blank
entries
there will be in a row nor do I know when there will be blank entries?

Does anyone know of a formula (not a macro) that can accomplish this
perplexing situation?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ttam78ouch" wrote in message
...
Thank you, I figured I was overlooking the obvious...works to perfection!

"T. Valko" wrote:

Try these:

Enter this formula in C2 and copy down as needed:

=IF(B2="","",IF(COUNT(B$2:B2)=1,B2,B2-LOOKUP(1E100,B$1:B1)))

Enter this formula in E2 and copy down as needed:

=IF(D2="","",IF(COUNT(D$2:D2)=1,D2,D2-LOOKUP(1E100,D$1:D1)))

--
Biff
Microsoft Excel MVP


"ttam78ouch" wrote in message
...
Is there a formula I can use to subtract meter readings to show usages.
For
Example:
A B C D E
1 Date Meter1 Usage1 Meter2 Usage2
2 1/1/01 25 25 10 10
3 1/2/01 30 5 26 16
4 1/3/01 30 4
5 1/4/01 36 ?
6 1/5/01 39 3 40 ?

In the crude example above the meter readings are manually put in where
the
usage formulas were IF formulas. However I am trying to get away from
using
IF formulas or nested IF formulas since I have no idea how many blank
entries
there will be in a row nor do I know when there will be blank entries?

Does anyone know of a formula (not a macro) that can accomplish this
perplexing situation?