formula help...thank you:)

Posted by Heidi on February 07, 2002 12:45 PM

Here is what I am trying to accomplish:
I have a row with a formual in it, some cells have the #DIV/0! or #Value! error in them. I want to replicate the value of that cell into another cell, unless the cell contains a formula error. If there is an error, I want it to put N/A in the new cell instead of giving me the #VALUE! error. Is that possible or is there some other way to automatically replace formula errors with "N/A". My errors are occuring because some months I won't have a value for every cell which will happen often and my boss doesn't like to see the formula errors in the report. There are so many errors that it would be time consuming to manual change them and remember to change them back if they had numbers the next month...

Posted by Aladin Akyurek on February 07, 2002 1:01 PM

Heidi --

I'd suggest to re-devise the formulas that compute an error-value, that is, expand them in such a way they do not return #DIV/0! or #VALUE!.

Lets say that you have a formula like

=A1/B1

If B1 is blank or has a literal 0 in it, you'll end up with #DIV/0!. If you change this formula to:

[1]

=IF(B1,A1/B1,NA()) or

[2]

=IF(B1,A1/B1,0)

you'll avoid #DIV/0!.

You can also custom format the cell of [1] as:

[=#N/A]"";General

the cell of [2] as:

[=0]"";General

If such changes would be a lot work, you can replicate into another cell the cell that contains an error as:

=IF(ISERROR(A1)),NA(),A1)

or as

=IF(ISERROR(A1)),0,A1)

and custom format the replicating cell (the cell of this formula) as suggested above.

Aladin

========

Posted by Heidi on February 07, 2002 2:45 PM

Ok, I htink I get the first 2 examples. However when I try to use that formula with theis formula I get errors (the box telling me I have too many arguments). What would it look like using
Average(c5:c50) instead of your division example.
I tried the following with no success:
=AVERAGE(c9:c50)*if(c9:c50,(),N/A) (excel put the asterisk in) but i still got div0
=average(c9:c50)if(C10>0)

I could have it just look at the total for that column and if that total is greater than zero then give me the average of my specified range. if it is zero than give me N/A. but I don't know what the formula would look like.

Where do you enter the cusotm formats? Do I just enter the formats including the brackets in the conditional formatting area?

Thanks you so much for helping:) I hate to quit bugging you..

Heidi -- I'd suggest to re-devise the formulas that compute an error-value, that is, expand them in such a way they do not return #DIV/0! or #VALUE!. Lets say that you have a formula like =A1/B1 If B1 is blank or has a literal 0 in it, you'll end up with #DIV/0!. If you change this formula to: [1] =IF(B1,A1/B1,NA()) or [2] =IF(B1,A1/B1,0) you'll avoid #DIV/0!. You can also custom format the cell of [1] as: [=#N/A]"";General the cell of [2] as: [=0]"";General If such changes would be a lot work, you can replicate into another cell the cell that contains an error as: =IF(ISERROR(A1)),NA(),A1) or as =IF(ISERROR(A1)),0,A1) and custom format the replicating cell (the cell of this formula) as suggested above. Aladin ======== : Here is what I am trying to accomplish:

Posted by Aladin Akyurek on February 07, 2002 4:05 PM

When this AVERAGE formula gives you a #DIV/0!, you have 2 possibilties: (a) C5:C50 contains at least a #DIV/0!, (b) there are no numbers in C5:C50 (that is, every cell is blank.

Change this average formula to:

=IF(COUNT(C5:C50),AVERAGE(C5:C50),0)

This formula says if there is any number in C5:C50, do AVERAGE, else give 0.

While in the cell of this formula, activate Format|Cells, choose Custom from the left-hand side list on the Number tab, and enter for Type:

[=0]"";General

If you still get #DIV/0!, search C5:C50 for a formula that returns #DIV/0! and modify that too.

======================= I tried the following with no success:

Posted by Heidi on February 07, 2002 4:11 PM

Thank you sooooo much I really appreciate it. Where did you learn all this stuff? Can you reccommend a book that I could use to improve my formula and function skills? You are a rocket scientist, it's awesome:) Thanks again..

Posted by Aladin Akyurek on February 07, 2002 4:29 PM

How or Where to learn/

Well, you could study the stuff I use for teaching my students, but that is still not in English...

You could look at amazon.com to see what is on offer. I think a book in between an intro and intermediate level would help.

========= Thank you sooooo much I really appreciate it. Where did you learn all this stuff? Can you reccommend a book that I could use to improve my formula and function skills? You are a rocket scientist, it's awesome:) Thanks again..