Ignoring #VALUE errors in a formula?

[noahd]

So I am using the following formula to take a data set and standardize it between 0 and 1:

=(U2-MIN(U$2:U$301))/(MAX(U$2:U$301)-MIN(U$2:U$301))

I have a data set I want to use the above formula on, BUT it has #VALUE ! errors in the set. I want the formula to just run the calculation for each normal piece of the data set and just ignore the #VALUE errors. I don't want to delete the #VALUE errors and I don't want to find and replace them with something else.

How do I alter the above formula to accomplish this?

 

[Smitty]

Welcome to the Board!

If you just want to ignore the error, and don't care about addressing the root cause, you can use IFERROR:

=IFERROR((U2-MIN(U$2:U$301))/(MAX(U$2:U$301)-MIN(U$2:U$301)),0)

Or substitute "" (nothing) for 0.

Just note that IFERROR is a blanket error handler, so it will suppress all errors.

HTH,

 

[noahd]

This does not seem to work.

When I enter =IFERROR((U2-MIN(U$2:U$301))/(MAX(U$2:U$301)-MIN(U$2:U$301)),0)

into the equation box it spits out

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {mso-number-format:"_-\0022$\0022* \#\,\#\#0\.00_-\;\\-\0022$\0022* \#\,\#\#0\.00_-\;_-\0022$\0022* \0022-\0022??_-\;_-\@_-";}--></style>
<nobr></nobr><nobr></nobr>[TABLE="width: 65"]
<colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
<!--StartFragment--> [TD="class: xl65, width: 65, align: right"] $ - [/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]


in the relevant excel cell. So something isn't working...

 

[Jonmo1]

I think you're going to have to adjust the formulas in U2:U301 so that they return 0 or even "" instead of #Value ! errors.
What is that formula?

 

[noahd]

The Formula there is:

=LEFT(TEXT(L2,"???/???"),FIND("/",TEXT(L2,"???/???"))-1)+0

It is taking the basketball statistic of FTM/FTA and extracting the FTM from that and putting it into this column. I don't want to eliminate the #value errors if possible because there are also 0s in the set that I DO want to count, but I DON'T want to count any 0's that are due to value errors because that will throw off the data.

 

[Jonmo1]

We don't have to make it return 0 instead of #Value !
We could make it return ""

=IFERROR(LEFT(TEXT(L2,"???/???"),FIND("/",TEXT(L2,"???/???"))-1)+0,"")

Then your Min and Max functions will not error due to the existence of #Value ! errors in the range.

 

[noahd]

Thank you! This worked!

 

[Jonmo1]

You're welcome.

 

[James0734]

So I am using the following formula to take a data set and standardize it between 0 and 1:

=(U2-MIN(U$2:U$301))/(MAX(U$2:U$301)-MIN(U$2:U$301))

I have a data set I want to use the above formula on, BUT it has #VALUE ! errors in the set. I want the formula to just run the calculation for each normal piece of the data set and just ignore the #VALUE errors. I don't want to delete the #VALUE errors and I don't want to find and replace them with something else.

How do I alter the above formula to accomplish this?

Create a new column and try formula =if(ISNUMBER(a2),b2,1) - this took out #value for me