Can't seem to find the answer to a simple solution?

[Mike_Schell]

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?

[#2]

@Mike_Schell 

In E10:

=LOOKUP(9.99999999999999E+307,E2:E9)

[#3]

@Hans Vogelaar Wow, fantastic!! Thank you!!  I have no idea why that works reading the code but it does. Awesome!!

[#4]

Hans,
=LOOKUP(9.99999999999999E+307,E2:E9)

Why "+307" in this code?? I am trying to get it to work in a totally different spreadsheet but having trouble getting it to come out right. Is the ...+307... germane only to the example I provided above or is it a universal value that can be used in any column application?

Thanks

[#5]

Or does the "E" after all the 9's have to match the particular column that I want to use the formula in? Whether in the same spreadsheet or a completely different one?

[#6]

@Mike_Schell 

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.

[#7]

@Hans Vogelaar 

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.

[#8]

@Mike_Schell 

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.

[#9]

@Hans Vogelaar 

"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!

https://www.dropbox.com/scl/fi/20teqg7sos317v27bf8lv/401K-Chart-Latest-NEW-version-1-.xlsb.xlsm?dl=0...

[#10]

@Mike_Schell 

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:

[#11]

@Hans Vogelaar 

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

[#12]

@Mike_Schell 

Change the formulas the same way I did for G2:G25.

[#13]

@Hans Vogelaar 

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

[#14]

@Mike_Schell 

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.

[#15]

@Hans Vogelaar 

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.