# ISERROR #VALUE ???

#### mvatoi

##### Board Regular
Hi,

Lets say the cell I'm working on is D3

D3 = A1+A3+A5 (It's a formula, and sometimes it returns "#VALUE")

I want cell D3 to have a formula and if the result in D3 ISERROR, put a value of 1 there.

I'm running into Circular Reference, and don't know how to do it. Any help?

============

Second,

From a long list of data and some of them have "#VALUE"

I want to take MAX, MIN, AVERAGE, but want to ignore the #VALUE

Like:

MAX(A:A) but ignore any cell in (A:A) that = "#VALUE"

THANKS ALL FOR HELPING

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Why not amend the formulae in A1, A3 and A5 so that they don't return an error?

Running with what njimack said, it would be easier to correct your formulas so they don't return error...

for the example in D3

=A1+A3+A5

put

=IF(ISERROR(A1+A3+A5),1,A1+A3+A5)

so If A1+A3+A5 results in error, it will return 1 instead of error..otherwise, completes the formula...

the basic syntax is

=IF(ISERROR(original formula here),1,original formula here)

I would assume that:

A1+A3+A5

returns the error because there is text in the range (?).

=SUM(A1,A3,A5) would solve that.

What does the 1 signify?

Hi,

Lets say the cell I'm working on is D3

D3 = A1+A3+A5 (It's a formula, and sometimes it returns "#VALUE")

I want cell D3 to have a formula and if the result in D3 ISERROR, put a value of 1 there.

I'm running into Circular Reference, and don't know how to do it. Any help?

============

Second,

From a long list of data and some of them have "#VALUE"

I want to take MAX, MIN, AVERAGE, but want to ignore the #VALUE

Like:

MAX(A:A) but ignore any cell in (A:A) that = "#VALUE"

THANKS ALL FOR HELPING

Question 1:

If A1, A3, and A5 house no error values, only numbers and/or formula blanks...

=SUM(A1,A3,A5)

Question 2:

Control+shift+enter...

=MAX(IF(ISNUMBER(A2:A100),A2:A1000))

=AVERAGE(IF(ISNUMBER(A2:A100),A2:A1000))

sorry for the confusing, A1, A3, A5 are not the actual cells i'm working on,

these cells represent a list of data I filitered from somewhere else (horizontal actually, like A1, B1, C1)

and then D1 is where the formula comes in place, it's an IF statement formula in cell D1

I have no control over filtered data, cause everyday the data will change

so if any cell in A1, B1, C1 contains error, sometimes or "word" instead of a #, I want to ignore it

==> The way I want is, IF the formula in cell D1 ="#VALUE", either place "", or return it with a given value, like 1

=======

If the first step is done, I do not need the second step.
But if the first step can't be done, ONLY the second step will work also

After I get a long list of date in column D

I want to take the MAX, MIN, AVERAGE

=MAX(D:D) but ignore all the cells that have "#VALUE"

Thanks for all the help

sorry for the confusing, A1, A3, A5 are not the actual cells i'm working on,

these cells represent a list of data I filitered from somewhere else (horizontal actually, like A1, B1, C1)

and then D1 is where the formula comes in place, it's an IF statement formula in cell D1

I have no control over filtered data, cause everyday the data will change

so if any cell in A1, B1, C1 contains error, sometimes or "word" instead of a #, I want to ignore it

==> The way I want is, IF the formula in cell D1 ="#VALUE", either place "", or return it with a given value, like 1

One of:

=SUM(SUMIF(A1:C1,{"<0",">0"}))

=IF(ISERR(SUM(A1:C1)),1,SUM(A1:C1))

=======

If the first step is done, I do not need the second step.
But if the first step can't be done, ONLY the second step will work also

After I get a long list of date in column D

I want to take the MAX, MIN, AVERAGE

=MAX(D:D) but ignore all the cells that have "#VALUE"

...

If still relevant, see my previous post.

Question 1:

If A1, A3, and A5 house no error values, only numbers and/or formula blanks...

=SUM(A1,A3,A5)

Question 2:

Control+shift+enter...

=MAX(IF(ISNUMBER(A2:A100),A2:A1000))

=AVERAGE(IF(ISNUMBER(A2:A100),A2:A1000))

First step can't be SUM, because it's mostly comparison and the cells contain dates
=IF(ISBLANK(A3),A2,A3-A1))
the actual formula is about 10 times longer than that with many nested IF statements in the same formula. And one in awhile the filtered data have a letter instead of a date and it result in "#VALUE"

The second step, I did your way with and w/o Control+Shift+Enter
=MAX(IF(ISNUMBER(A2:A100),A2:100))

but it returns "0" instead of an actual MAX# in see in the data

Thanks

Question 1:

If A1, A3, and A5 house no error values, only numbers and/or formula blanks...

=SUM(A1,A3,A5)

Question 2:

Control+shift+enter...

=MAX(IF(ISNUMBER(A2:A100),A2:A1000))

=AVERAGE(IF(ISNUMBER(A2:A100),A2:A1000))

First step can't be SUM, because it's mostly comparison and the cells contain dates
=IF(ISBLANK(A3),A2,A3-A1))
the actual formula is about 10 times longer than that with many nested IF statements in the same formula. And one in awhile the filtered data have a letter instead of a date and it result in "#VALUE"

The second step, I did your way with and w/o Control+Shift+Enter
=MAX(IF(ISNUMBER(A2:A100),A2:100))

but it returns "0" instead of an actual MAX# in see in the data

Thanks

A2:100 should have been A100...

What does...

=MAX(IF(ISNUMBER(A2:A100+0),A2:A100+0))

return?

A2:100 should have been A100...

What does...

=MAX(IF(ISNUMBER(A2:A100+0),A2:A100+0))

return?

I got step 1 figured out.
Just one more step to make it perfect. How do I make the formula alert me if the cell contains an error?

For example, =IF(ISERROR(D3),CHANGE CELL COLOR TO YELLOW?)

is that possible? I already have the cell conditional formatted in 3 different color for other purposes that is more necessary, so can't use anymore conditional formatting.

This alerting purpose just an extra step, so that I know the cell contains error, and it takes a given value instead of its actual value.

==========

The second step, I still couldn't figure out

=MAX(A2:A100)

the formula above will ignore any errors EXCEPT "#VALUE!"

For example, I have the following:

Column A:
5
8
R
TY
#VALUE

=MAX(A1:A5) will returns 8, it ignores all error
=MAX(ISNUMBER(A1:A5),A1:A5) will returns 8 also

=MAX(IF(ISNUMBER(A1:A5),A1:A5)) and
=MAX(IF(ISNUMBER(A1:A5+0),A1:A5+0)) << both formula return 0

but if I change the last value to #VALUE! (just an extra exclamation mark at the end)

=MAX(A1:A5) now returns "#VALUE!"
=MAX(ISNUMBER(A1:A5),A1:A5) returns #VALUE! also

The other 2 formula do not change, they both returns 0

Another issue I found on the spreadsheet I work on is in 1 cell, the formula will calculate ONLY if the cell contains a #, a letter won't work for MAX, MIN, or AVERAGE, I do not know why either, I cleared, reformatted the cell, and the same thing happened (but only 1 cell).

The EXCLAMATION MARK does NOT work for ANY/ALL cells.

I tested it on 3 different computers w/ 3 different versions of Excel, Windows version 2002, Windows version(not sure, computer not present now), and MAC version 2004, and none works.

Replies
2
Views
170
Replies
2
Views
525
Replies
4
Views
263
Replies
3
Views
222
Replies
10
Views
701

1,221,216
Messages
6,158,585
Members
451,501
Latest member
andysacko

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back