Jan 23 2022 06:43 PM
Jan 23 2022 06:43 PM
I am trying to leave a cell blank until there is a value in another cell. Example:
Cell D11 has a formula: =SUMIF(ADV.1!$B$3:$B$186,Summary!D3,ADV.1!$E$3:$E$186)
In Cell D12 I want to calculate the running bal showing Year Budget - Amount Spent in cell D11:
But I don't want the value in Cell D12 to show until there is a number in Cell D11. I've tried the "IsBlank" but because there is a formula in D11 it isn't showing as blank and keeps showing a number in D12.
Could you help?
s showing a number in D12.
Could you help?
Jan 23 2022 08:04 PM
Ostensibly, you might write:
=IF(D11="", "", C10-D11)
=IF(AND(D11<>"",C10<>""), C10-D11, "")
Note that those formula do __not__ "leave the cell blank" per se. They make the cell __appear__ blank.
In fact, they contain a value when they appear blank, namely the null string (text). Consequently, it might be tricky to reference them in some arithmetic expressions. Also, ISBLANK would return FALSE because that function really tests for __empty__ cells; that is, cells with no value.
However, your description is confusing because if D11 contains that SUMIF formula, there is always a value in D11. It might be zero.
So if the formulas above do not solve your problem, or they create new ones that you cannot solve yourself, attach an Excel file, not a screen image. See the "browse" link at the bottom of the reply pane.
If you not see the link (I'm told that some users do not), upload the Excel file to a file-sharing website, and post the public download URL. I like box.net/files; others like dropbox.com.
Jan 23 2022 08:29 PM
@Joe User this did not solve the problem. I am attaching the spreadsheet for reference. I need columns D - O, rows 8, 9, 13,14 to be blank until there are values in rows 6 or 7. Currently I have formulas in rows 6 & 7.
Jan 23 2022 10:23 PM - edited Jan 23 2022 10:24 PM
@Rosiid Have slightly changed the set-up of the schedule and changed the formulae in rows 8 and 9, trusting that you can do the rest yourself, following the same principles. Added some data in ADV.1 to test.
Jan 24 2022 12:06 AM
@Rosiid wrote: ``I need columns D - O, rows 8, 9, 13,14 to be blank until there are values in rows 6 or 7. Currently I have formulas in rows 6 & 7.``
Thank you for providing the Excel file. Unfortunately, I do not understand what you are trying to do.
In particular, I do not understand the distinction you are trying to make by saying "until there are values in rows 6 or 7".
As you note, you have formulas in rows 6 and 7 that always return a numeric value.
I can only imagine (wild guess) that you want to treat zeros as "not a value". The zero value appears as a dash ("-") because of the Accounting format. In that case, your formulas might be:
D8: =IF(D6*D7=0, 0, SUM($C$5)-SUM(D6:D7))
D9: =IF(D6*D7=0, "", =SUM(D6:D7)/$C$5)
D13: =IF(D12=0, 0, C11-D12)
D14: =IF(D12=0, "", D13/$C$11)
You do not have a formula in D14. So my formula above is wild speculation.
The expression D6*D7=0 is a tricky way of saying OR(D6=0,D7=0). That is, D6*D7 is zero if either value is zero.
The formulas in D9 and D14 return the null string ("") because I assume that you do not want to see 0%. If 0% is acceptable, no change is needed to D9 and presumably to D14.
OTOH, if you would like to see a dash, you can use your original formula in D9, but with the following Custom format: [=0]"-"; 0%
Be that as it may, it looks like @Riny_van_Eekelen has jumped in to help. Follow his lead, since I do not have the patience to contribute further.
Jan 24 2022 12:20 AM - edited Jan 24 2022 05:59 AM
@Riny_van_Eekelen .... For your edification, there is no reason to use double-negate ("--") in expressions like =($C$8-SUM($C$6:D7))*--(SUM(D6:D7)<>0) in D8.
I know that your intent is to coerce TRUE and FALSE into 1 and 0.
But there is nothing special about double-negation for that purpose. __Any__ arithmetic operation accomplishes that. Double-negation is simply "one" (really two) arithmetic operation(s).
Likewise, the multiplication suffices as __one__ arithmetic operation. So, you can write simply =($C$8-SUM($C$6:D7))*(SUM(D6:D7)<>0) .
But it is probably more efficient, albeit not as terse, to write
IF(SUM(D6:D7)<>0, $C$8-SUM($C$6:D7), 0) .
The IF operation is not a true function. It does not evaluate all of its "parameters", then "call" an internal IF "function". Instead, it evaluates the "parameters" left-to-right, stopping when the first "true" expression is evaluated. In effect, the IF "function" is an __operator__ that has the __form__ of a function.
(FYI, that is __not__ true of the new IFS function. Sigh.)