General discussion

Excel - Ignoring #N/A value in calculations

Hi. I am trying to get excel to return the calculated average of cells in a row while excluding cells with #N/A value in the calculation. I do not want to end up with a returned value of #N/A because I have #N/A value cells in the row. My #N/A values are the result of a VLOOKUP function.
Could anyone suggest an approach? Thank you for your help on this.

Discussion is locked

Follow
Reply to: Excel - Ignoring #N/A value in calculations
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: Excel - Ignoring #N/A value in calculations
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Comments
- Collapse -
the answer is in the Excel Help

I don't have Excel here, to answer you with specifics, but the last time I ran into that I found out how to stop the "N/A" displaying by just looking in the Excel Help.

I believe it had to do with conditional formatting, and to tell the cell to display "" (nothing, as what is between the quotes) when the otherwise error condition exists.

Good luck, I am sure you can find it. If not then I suggest that you simply Google it. I did and found a few thousand hits.

- Collapse -
Is it that easy?

thanks I'll see if conditional formating will resolve my problem.

- Collapse -
Yes it should be that easy

eg;

If B7 holds the formula =7/J (This will force a #NAME error).

In C7 the formula =IF(ISERROR(B7),"Mark",B7) and this cell will display Mark

The function ISERROR checks to see IF(there is an)ERROR in B7. If so, then Mark will be displayed. If not, the value of B7 will be displayed.

Excel help will display error values if you type in #N/A and you can use the ISERROR function to workaround cells displaying error codes.

Mark

CNET Forums

Forum Info