If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Blank" cells showing unwanted data
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. |
#2
|
|||
|
|||
"Blank" cells showing unwanted data
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. "Ed O'Brien" wrote: 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. |
#3
|
|||
|
|||
"Blank" cells showing unwanted data
Sorry, Ed, but this is not clear.
What was the original SUM formula? The one you will always return the same value. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Ed O'Brien" wrote in message ... 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. |
#5
|
|||
|
|||
"Blank" cells showing unwanted data
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 "Mike H" wrote in message ... 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. "Ed O'Brien" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|