Solved

# I need to update the following formula

Posted on 2013-11-06
184 Views
I posted this question yesterday and thought it was answered correctly. In some cases, there are still zeroes being displayed.

I need to update =IFERROR(('Projects'!K44),"") to return a blank if there is a value of Zero in the sheet it is linked to. I have tried the following with no luck:

1. Turned off zeros in excel options
2. =IFERROR(IF('Projects'!M10=0,""),"") RETURNS FALSE
3. =IFERROR(1/(1/(Projects!M10)),"") Wipes out any data that was in the field, returns no information.
0
Question by:wrt1mea

LVL 48

Expert Comment

ID: 39627439
Hi,

pls try
``````=IFERROR(IF(Projects!M10=0,"",Projects!M10),"")
``````
Regards
0

LVL 43

Expert Comment

ID: 39627625
This will take care of 0 entered as text

=IFERROR(IF(VALUE(Projects!M10)=0,"",Projects!M10),"")
0

LVL 1

Author Comment

ID: 39627840
Rgonzo1971...

I tried your formula and it did not remove the zeroes....FYI, these are referencing text.  The other cells where I am referencing number values, I have no problems.

Ssaqibhy...

I tried your formula and it completely wiped out all of the text. Most of the information I am referencing is text. No issues with referencing numbers....
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
ID: 39627928
Ok lets see how this one behaves

=IFERROR(IF(SUM(--(Projects!M10={0,"0"})),"",Projects!M10),"")
0

LVL 50

Expert Comment

ID: 39628231
I imagine this will work for text or numbers

=IFERROR(IF('Projects'!M10="","",'Projects'!M10),"")

regards, barry
0

LVL 1

Author Closing Comment

ID: 39628312
Works perfectly...

Barry....your version returned the zero...Hope all is well!!!
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…