ISERROR #VALUE ???

mvatoi

Board Regular
Joined
Jul 21, 2007
Messages
55
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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
Why not amend the formulae in A1, A3 and A5 so that they don't return an error?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Aug 12, 2005
Messages
976
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

Board Regular
Joined
Jul 21, 2007
Messages
55
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

Board Regular
Joined
Jul 21, 2007
Messages
55
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

Board Regular
Joined
Jul 21, 2007
Messages
55
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!!!
 

Forum statistics

Threads
1,181,102
Messages
5,928,072
Members
436,586
Latest member
latintxn

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top