Data totals corupted by formula

[help needed yes please]

Column A B
A47 2111921 ColumnB:162
A48 2112093 ColumnB 156
.................... B49-2112093 'B'has moving formula =Sum(A47-A46) (A48-A47) and so on. column 'B' is used toprovide sequential total elsewhere.
How can I stop this -2112093 figure (Which of course changes with every newinput into column 'A') From corrupting the periodical data collected elsewhere?Sorry if this is not right. Never done this before and cant find a way to sendyou jpg of my work page. Only found a url input. Pulling hair out here. Tafrank<o></o>

 

[RoryA]

Welcome to the forum.

You can just test if column A is empty:

=IF(A47="","",A47-A46)

 

[help needed yes please]

Thanks Rory, it is precisely because 'A' is empty that the figure is carried over into 'B' using the current formula. I tried your suggestion and excel would not accept it. I have tried the A1="", bit before as part of an IF and excel will not accept it as code for recognizing it as an empty cell. Been on this for days and days, stay with me please Rory. Thanks Frank

 

[RoryA]

The only way that formula would be invalid is if you use different separators in your formulas. Does this work for you?

=IF(A47="";"";A47-A46)

 

[help needed yes please]

Hi Rory, did as suggested, no go though. The cell E48 has number 172 showing. E49 has 2112093 I clear this cell and enter =IF(D49="";"";D49-D48) this brings popup 'Error' bottom left corner of screen shows 'Edit' cant get away from cell while that formula still in it. I am trying to get a '0' in column E when the cell beside it in D is empty. Not there yet but thank you very much for trying to help me out, having some help is wonderful, thank you, frank [TABLE="width: 120"]
<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"><tbody>[TR]
[TD="class: xl66, width: 80, bgcolor: transparent"] 2111765[/TD]
[TD="class: xl68, width: 80, bgcolor: transparent, align: right"]162[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2111921[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]156[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2112093[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]172[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"] 2112093[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

And what happens with

=IF(D49="","",D49-D48)

in that cell?

 

[help needed yes please]

Frank, Hi Rory[TABLE="width: 120"]
<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <tbody>[TR]
[TD="class: xl64, width: 80, bgcolor: transparent"]2111765[/TD]
[TD="class: xl66, width: 80, bgcolor: transparent, align: right"] 162 [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2111921[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]156[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2112093[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]172[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
E48 has number 172 in it which is generated with
=IF(D48="","",D48-D47) Gives right answer in this cell of E48. placing same formula in cells below E48 gave this result [TABLE="width: 120"]
<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <tbody>[TR]
[TD="class: xl66, width: 80, bgcolor: transparent"]2112093[/TD]
[TD="class: xl68, width: 80, bgcolor: transparent, align: right"]172[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2113050[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]2,113,050[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]-2,113,050[/TD]
[/TR]
</tbody>[/TABLE]
E48 172, E49 blank, E50 same as D50, E51 returned same answer even though D51 was empty. (Worked on giving right answer of 171 (E48) yet did not when it gave answer 2113050 (E50) perhaps because D49 was empty? I tried all combinations on a different computer and get same results so I think my excel program is ok. Tried various cells same result. Tried copy and paste formula direct from this page , made no difference. Trying seems to be the order of the day Rory, thank God we are alive eh? thanks, F

 

[help needed yes please]

Frank, Hi Rory back to bubble and strife. Numerals in Column A. Formula results in Column B whichhas =IF(A4="","",A4-A3) It Works NOW! As seen in mock upshown when applied to a blank cell returns ‘Blank’ cell in ‘E’ However if a day’sentry is absent in A4 following results go wonky. I have found that if I copy A3into A4 then B results come good. So I am left with two things? Cant auto totalcolumn B if gaps in listing. Need it to place a ‘0’ in B. The other is how tomake it work the same as B1 to B3 and pick up again after a gap in A column.Rory I mucked on earlier tries because in my mock up I had gaps all over theplace which I am pretty sure caused me lots of grief. Sorry, my bad, I am sorryyour kindness has been so ill used by my stupidity!@#@!@# (I don’t swear butyou get the drift?) Sorry mate. I have now I think made it easier tounderstand?
[TABLE="class: MsoNormalTable, width: 96"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]

2112210<o></o>​

[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

2112230<o></o>​

[/TD]
[TD="width: 64, bgcolor: transparent"]

20<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

2112245<o></o>​

[/TD]
[TD="width: 64, bgcolor: transparent"]

15<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]

2112264<o></o>​

[/TD]
[TD="width: 64, bgcolor: transparent"]

2112264<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<o></o>

 

[help needed yes please]

frank to Rory. Yes somehow after renewing page and trying =IF(D49="","",D49-D48) it works now. I think I had to many empty cells scattered in my mock up page and confused excel. Anyway ITS ALL GOOD Thank you for your patience and help really appreciated it. Ta F

 

[help needed yes please]

Frank to Rory. thanks you were great with your advice, keep the faith, ta Frank