"Blank" cells showing unwanted data

[Ed O'Brien]

I inherited a workbook for energy usage in which the formula below was
entered weekly as each week's readings were entered. Rather than do this
each week I dragged the formulae down each column so all I would need to do
was enter the reading each week. Simple? Yes, I thought so too.

However, I changed the =SUM formulae to =IF(L29=" "," ",7*2.466*0.21294)
to prevent blank readings such as #VALUE showing when there are no entries
in the row.

My problem is, dragging the formula down the columns 5 to 57, 3 out of 6
columns - in the same rows, 34 to 46 - the last entry made in row 13 of that
column displays when the cell should be blank. All other cells behave as
expected - blank.

All the rows and columns are identical except, of course, the cell numbers
within the formulae.

Can anyone explain how this happens and if there is a way I can cure it?

TIA or any help.

 

[Mike H]

Hi,

I'm a bit confused by the terminology here

My problem is, dragging the formula down the columns 5 to 57, 3 out of 6
columns - in the same rows, 34 to 46 -

I'm not sure I understand that, when dragging 'down' isn't it 'Rows'?

However if the formula doesn't show blank that means there is something in
the cell being referred to and if someone has written a formula like yours
than that could be the answer. Your formula

=IF(L29=" "," ",7*2.466*0.21294)

Doesn't look for a blank it looks for a space and would be better written
like this

=IF(L29="","",7*2.466*0.21294)

Note we are now looking for a null string "" ( and not " ") and returning a
null string. the problem in doing it your way is if a cell contains a space,
it 'looks' blank but to Excel it's not.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Bernard Liengme]

Sorry, Ed, but this is not clear.
What was the original SUM formula? The one you will always return the same
value.
best wishes

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Ed O'Brien]

Thanks, Mike.

I understand now. However, using "","", causes the last entry to repeat in
every cell down the whole column. I decided this is because the L29 entry
is from it's own formula - not typed in. I resolved it by selecting a cell
in the row where the entry is typed and not the outcome of a formula.

Sorry for slowness in coming back. I've just upgraded the PC to Windows 7
only to find I can't get the newsgroups. I'm having to dig out my old laptop
(XP).

Seems a backward step to me!

Ed