SOLVED
Home

Blank Not equal to ""

%3CLINGO-SUB%20id%3D%22lingo-sub-325330%22%20slang%3D%22en-US%22%3EBlank%20Not%20equal%20to%20%22%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325330%22%20slang%3D%22en-US%22%3E%3CP%3EIn%26nbsp%3B%20a%20formula%20for%20one%20cell%20it%20tests%20to%20see%20if%20value%20%3D%22%22.%20If%20so%20it%20set%20a%20different%20cell%20to%20%22%22.%20Now%20in%20a%20separate%20formula%20that%20is%20applying%20a%20mathematics%20formula%20based%20on%20several%20cell%20values.%20For%20the%20ones%20where%20the%20value%20has%20never%20been%20set%20i.e.%20blank%20the%20calculation%20returns%200%20as%20the%20cell%20value%20as%20the%20expression%20is%20evaluated.%20But%20for%20the%20cell%20where%20the%20value%20was%20set%20to%20%22%22%2C%20the%20value%20returned%20as%20part%20of%20the%20expression%20evaluation%20is%20%23VALUE!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20is%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrying%20to%20work%20around%20if%20a%20cell%20was%20blank%20instead%20of%20setting%20to%20%22%22%2C%20I%20tried%20setting%20the%20new%20cell's%20value%20G7%20to%20that%20of%20the%20blank%20cell.%20That%20set%20the%20new%20cell%20(G7)%20to%200%20not%20blank%20e.g.%26nbsp%3B%3DIF(ISBLANK(F7)%2CF7%2CC7-F7)%20where%20F7%20is%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20do%20further%20math%20using%20G7%2C%20but%20I%20want%20the%20blank%20to%20be%20treated%20like%200%2C%20but%20not%20show%20as%200.%20I%20am%20trying%20not%20to%20have%20to%20create%20messy%20formula%20that%20has%20to%20test%20every%20cell%20in%20the%20formula%20for%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-325330%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325385%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20Not%20equal%20to%20%22%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325385%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Wyn%2C%20the%20N()%20option%20worked%20great!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325348%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20Not%20equal%20to%20%22%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325348%22%20slang%3D%22en-US%22%3ECould%20you%20change%20the%20original%20formula%20to%20show%200%20and%20then%20just%20format%20it%20as%20a%20blank%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAlternatively%20wrap%20your%20reference%20to%20the%20cell%20containing%20%22%22%20in%20N(%20)%20e.g%2C%20N(%20A2%20)%20will%20convert%20%22%22%20in%20A2%20to%200%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
RickGatorB
New Contributor

In  a formula for one cell it tests to see if value ="". If so it set a different cell to "". Now in a separate formula that is applying a mathematics formula based on several cell values. For the ones where the value has never been set i.e. blank the calculation returns 0 as the cell value as the expression is evaluated. But for the cell where the value was set to "", the value returned as part of the expression evaluation is #VALUE!

 

Why is that?

 

Trying to work around if a cell was blank instead of setting to "", I tried setting the new cell's value G7 to that of the blank cell. That set the new cell (G7) to 0 not blank e.g. =IF(ISBLANK(F7),F7,C7-F7) where F7 is blank.

 

My goal is to do further math using G7, but I want the blank to be treated like 0, but not show as 0. I am trying not to have to create messy formula that has to test every cell in the formula for blank.

 

Any help will be appreciated.

 

Thanks

2 Replies
Solution
Could you change the original formula to show 0 and then just format it as a blank?

Alternatively wrap your reference to the cell containing "" in N( ) e.g, N( A2 ) will convert "" in A2 to 0

Thanks Wyn, the N() option worked great!!!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies