Running Total using IF function

Question

Do i use an If function to display a running total: J2 (todays total yet to be calculated) will be equal to J1 (yesterdays total) plus I2 (todays new entry) but if I2 shows no entry, then J2 shows nothing as well. I only want J2 to show data when I2>0. This way I can set up my running total formula without all my J cells showing the same total as the day before until the I cells are populated with that days entry. Thank you. I kept getting a circular reference.

Answer 1

Thank you for taking the time to respond. When I saw your solution and implemented it, it worked perfectly. I apologize in the delay of my response but I just got back to tackling this again today. I really did spend HOURS trying to figure it out again today!. I think my error was partly due to entering a 0 or >0 or>-1. I studied what you did so I could try and figure out someting like this next time on my own. Thank you so much.

Answer 2

Hi,

=if(I2="","",I2+J1)

--

Regards,

Ashish Mathur

Microsoft Excel MVP

www.ashishmathur.com

"2010ExcelUser" wrote in message news:81f65b63-8f78-421d-afdc-03d463a95bfb...

Do i use an If function to display a running total: J2 (todays total yet to be calculated) will be equal to J1 (yesterdays total) plus I2 (todays new entry) but if I2 shows no entry, then J2 shows nothing as well. I only want J2 to show data when I2>0. This way I can set up my running total formula without all my J cells showing the same total as the day before until the I cells are populated with that days entry. Thank you. I kept getting a circular reference.

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Answer 3

It is not clear to me from you description whether Column I cells might be skipped in between values. If that is the case, I think putting this formula in J2 and copying it down as far as you like should work...

=IF(I2="","",$J$1+SUM($I$2:I2))

Otherwise, Ben's formula should work.

Answer 4

An easier way is to use the obvious formula in J2 (i.e. =J1 + I2 ) and then apply conditional formatting.

Conditional formatting using a formula "=I2=0 " and applying (say) white coloured text will give you the result you want.  Make sure not to use absolute references ($ signs) when specifying the formula for the conditional formatting.

This will still work if a blank value appears in the middle of the I column, whereas an IF formula might run into trouble with a missing value.

---

Andrew - www.stoptyping.co.uk

Answer 5

How about

=IF(I2<>"", J1+I2,"")

That will return a blank result if I2 is blank.  That allows for the possibility that I2 could actually BE zero.  Until you type a value into I2 though J2 will be blank.  Once you type a value it'll add J1 to I2.

---

-B-

http://www.officeforlawyers.com

Author: The Lawyer's Guide to Microsoft Outlook