Jul 16 2022 02:52 PM
Hello,
Looking for a formula to display a changing value to show up in one particular cell:
"E" column is a running total. What formula can I use in E10 (for example) to display to results of the running total without adding the numbers together.
For Example, E6 is 544. I know the simple code to display that particular value in E10 is =E6.
But, when I add a new line next week (with a new value showing up in E7, now), how can I get E10 to display that new value while dismissing the previous week's total?
Jul 16 2022 02:58 PM
Jul 16 2022 08:30 PM
@Hans Vogelaar Wow, fantastic!! Thank you!! I have no idea why that works reading the code but it does. Awesome!!
Jul 16 2022 09:28 PM
Jul 16 2022 10:02 PM
Jul 16 2022 11:58 PM
The E indicates so-called exponential notation.
9.99999999999999E+307 is Excel's notation for 9.99999999999999*10^307.
It is the largest number that you can enter into an Excel cell, i.e. larger than any number you want the formula to return. LOOKUP searches for this number, and since it cannot find it, it returns the last number it found.
Jul 17 2022 09:42 PM
I did a copy/paste of the code into cell G26 of another spreadsheet, but no value is showing up. Is there a reason why? The value in G26, in this example, should read 2,295 (G5).
Could it have something to do with the values that appear in this column already have a code for each of their individual cells? =SUM(E2*0.8)... E3, E4, etc. up to E25.
Jul 18 2022 12:33 AM
No, it shouldn't matter that G2 and below contain formulas:
Are you sure that G26 hasn't been formatted with white text?
If not: Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
Jul 18 2022 05:14 AM
"White text" would make sense. I checked and the text is "black". I don't mind sending a copy of the workbook. It is just numbers and no real sensitive data like account numbers or anything. I assume you would need the .xslb file correct?
Here is a Dropbox link. Let me know if this works. Thanks!
Jul 18 2022 05:47 AM
Thanks. That clears it up, The result of the formula in G6:G25 is 0, so the formula in G26 returns the value of G25, i.e. 0. But you have set the sheet not to display a zero in cells that have zero value, so the cell looks blank.
Solution: change the formula in G2 to
=IF(E2="","",E2*0.8)
and fill down to G25. G6:G25 will now contain the text value "" instead of the number 0. G26 will then display the expected value:
Jul 18 2022 05:33 PM
Ok, that worked for Column "G"...but how do I then get rid of (not show) all the other "zeros" that are now in my spreadsheet? Can it be done in a simplified manner? I rather go back to the old way if I have to manually fix every single column.
Appreciate the help! Thanks
Jul 18 2022 11:29 PM
Change the formulas the same way I did for G2:G25.
Jul 19 2022 07:28 PM
Sorry, must not be doing something right. Can't seen to get it to work.
So, to get the zeros to "disappear" in column "D", starting with D6 in this case, the new formula in D6 should be
=IF(D6="","",SUM(B6:C6) ...and then fill down to D25? Also, for this column I do not need to see the running total at the bottom like before, which would be D26, I just don't want to see the zeros if I haven't entered any data for that line yet.
Thanks
Jul 20 2022 01:41 AM
No, that wouldn't work - a formula in D6 cannot refer to D6 itself.
In D2:
=IF(SUM(B2:C2)=0,"",SUM(B2:C2))
Fill down to D25.
Jul 20 2022 06:13 PM
And the puzzle continues...
I entered =IF(SUM(B2:C2)=0,"",SUM(B2:C2)) in D2 and filled down just to D5 to start.
The fill color, font and size changed in D3-D5 but I know how to fix that. Also the values in F3-F5 didn't change and they are correct.
But...when I went back and filled down to D6 (as evident by the white box), the zero disappeared which is what I want ...BUT...I get an error in F6. Obviously, I would like all the zeros in column F to not show as well until I enter values for the line. Does the formula for column F need to change? Is it similar to the formula you gave me for column D?
I mean, Excel does so much! It amazes me that a simple thing like not showing zeros and still wanting to have a running total at the bottom would be less complicated.