Running Totals

[Ed]

Hi, everyody.

I have prepard a worksheet that logs electicity meter readings. Against
each reading I want the previous added to the current so I can view a
running total of the electricity used for the year each week.

Example: Column G shows the KWH used for that week.
Column H shows the number of KWH used as a running total.

This works okay with (H2)+(G3) H2 being the previous week's total.

My problem is, when auto inserting the whole year's column the last
entry shows all the way to the bottom of the worksheet.

Is there a formula I can use so that the part of the column not yet
arrived at remains empty?

TIA

Ed

 

[Ian]

=IF(G3="","",H2+G3)

 

[Ed]

Hi, Ian.

Thanks for the input. I've tried this and with some variations but it
does not give me what I'm looking for. I still get the last real total
repeated all the way to the bottom of the column. It's not a serious
problem, just that it's unsightly and I would like it to look better.

I don't think I'm explaining myself very well. Can I send a work sheet
example to your e-mail address as an attachment?

Ed

 

[Ian]

Are you sure column G entries are actually empty before entering the data?
If they are actually 0 instead, then you need to change my formula to read
as follows:

=IF(G3=0,"",H2+G3)

 

[Ed]

When I first saw this I thought - Eurika! - but not to be, I'm afraid.

G3 - and any of the cells for the week following - is empty and H2 is
the last total entered. (The current week)

I think we're understanding each other, but this may help....
Wk Reading 1 Units Reading 4 Units Kwh
35 62031 461 22924 11 472 472
36 62592 562 22936 12 574 1046
37 63155 563 22947 11 574 1620
38 63920 765 22957 10 775 2395
39 64685 765 22968 11 776 3171

The figure 3171 is then repeated all the way to week 34 next year.

Thanks for your patience.

Ed

 

[Ian]

I don't know if it's significant, but your data is wrong. Under Reading 1,
the 2nd entry should be 561.

The only way I get the result you report is if I use the format
=IF(G7="","",G7+H6) when G7 contains a formula. If column G contains a
formula then the cells are not blank, even if they appear so. The 2 options
are:
=IF(G7="","",G7+H6)
=IF(G7=0,"",G7+H6)

The second formula works fine whether colum G is blank or contains a
formula.

 

[Ed]

B C D E F
G H
cell Wk Reading 1 Units Reading 4 Units Kwh
3 35 62031 461 22924 11 472 472
4 36 62592 561 22936 12 573 1045
5 37 63155 563 22947 11 574 1619
6 38 63920 765 22957 10 775 2394
7 39 64685 765 22968 11 776 3170
8 40 0 3170
9 41 0 3170
10 42 0 3170


Hi, Ian...

Columns C,D, E & F are manually entered.
Column G automatically adds D & F
Column G has the basic formula: D3+F3 running
Column H has the basic formula beginning with cell H4: (H3)+(G4)
running. (H3 merely repeats G3)

I have been placing your formula in cell H8 in order to have all cells
from H8 downwards 'blank' until an entry appears in cell G8. Then only
G8 records a total.

I really appreciate your patience. If it gets too OTT, I'll understand.

Best wishes,

Ed

 

[Ian]

OK. Since column G adds the contents of D & F, then it can never be blank,
so there's no point in looking for "" with the If condition. Assuming both D
& F on the same row are blank (or zero), the column G will be 0. So the
formula in H8 should read =IF(G8=0,"",G8+H7). Reading this in words, it says
that if the value of G8 is zero, then this cell (H8) is blank, otherwise
this cell should equal the value of G8 (the cell to the left) plus the value
of H7 (the cell above).

If this doesn't work, then I really can't understand where it's going wrong.

 

[Ed]

I think this is the difficult part, Ian. H7 has a total - 3170. I have
tried, like a shot in the dark, and made it G8-H7, but with no effect.

Ed

 

[Ian]

It doesn't really matter what is in H7. If G8=0 then H8 should be blank.
This isn't making sense. Are you absolutely sure G8 =0 or is blank?

 

[Debra Dalgleish]

Is calculation set to Automatic? To check, choose Tools>Options.

 

[Ed]

Yes, Debra. It's set at 'Automatic'.

Ed

 

[Ed]

Yes, Ian. It has the simple formula: (D8+F8) but as D8 and F8 are both
blank so is G8.

Ed