ISERROR #VALUE ???

[mvatoi]

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

 

[njimack]

Why not amend the formulae in A1, A3 and A5 so that they don't return an error?

 

[Jonmo1]

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

for the example in D3

instead of

=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)

 

[ExcelChampion]

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?

 

[Aladin Akyurek]

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))

 

[mvatoi]

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

 

[Aladin Akyurek]

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.

 

[mvatoi]

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

 

[Aladin Akyurek]

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?

 

[mvatoi]

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.

THANK YOU FOR YOUR HELP!!!