Good afternoon
I have been asked by our packing department for a simple worksheet that deducts daily amounts of boxes used from a starting total. I have to a fashion done this but I don't like the fact that the same total shows (as of course it would) right through to the end of the month until new stock is removed. Please see screenshot
The formula is simply =E3-B4 and F3 - C4 and then copied down, Can I add anything to the formula so that nothing shows until a value is put into the B or C column, please note though that some days nothing is used but I could have themn enter a 0 if something had to be put in.
Thanks
Running total (excel 2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Running total (excel 2003 SP3)
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Running total (excel 2003 SP3)
Enter the following formula in E4:
=IF(COUNT(B4:B$34)=0,"",E3-B4)
Fill right to F4, then down to E34:F34. (Take care not to fill down to row 35!)
=IF(COUNT(B4:B$34)=0,"",E3-B4)
Fill right to F4, then down to E34:F34. (Take care not to fill down to row 35!)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Running total (excel 2003 SP3)
Hi Hans
Thanks for that. I fear that I have misunderstood your instruction when trying to implement it in the actual WS. I thought that I had replicated the instructions correctly but I still get the numbers showing when I enter 1 value.
Sorry to be a pain but can you take a look at the attachment for me because I can't see where I have gone wrong
Thanks for that. I fear that I have misunderstood your instruction when trying to implement it in the actual WS. I thought that I had replicated the instructions correctly but I still get the numbers showing when I enter 1 value.
Sorry to be a pain but can you take a look at the attachment for me because I can't see where I have gone wrong
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Running total (excel 2003 SP3)
Your formula in O7 is
=IF(COUNT($I$7:$I$37)=0," ",O6-I7)
This should be
=IF(COUNT(I7:I$37)=0,"",O6-I7)
Note that only the row number 37 has been made absolute by prefixing it with a $, the rest is relative. You made the entire range $I$7:$I$37 absolute, meaning it never changed in the formula from row to row.
=IF(COUNT($I$7:$I$37)=0," ",O6-I7)
This should be
=IF(COUNT(I7:I$37)=0,"",O6-I7)
Note that only the row number 37 has been made absolute by prefixing it with a $, the rest is relative. You made the entire range $I$7:$I$37 absolute, meaning it never changed in the formula from row to row.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Running total (excel 2003 SP3)
Hi Hans
Thank you, sometimes I think I am going backwards at this Excel malarky I would love to have enough time to use it daily then some of the advice and tips might just stick!
Thank you, sometimes I think I am going backwards at this Excel malarky I would love to have enough time to use it daily then some of the advice and tips might just stick!
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin