3 Replies to “How to Ignore #N/A Values When Using Formulas in Excel”

1. The IFNA turns #NA values into blanks, but blanks (empty cells) into zeros. That might be ok for SUM, it is a problem for AVERAGE, MEDIAN, STDEV and things like VAR.S.

   I solved it like this:
   =AVERAGE(IF(ISNUMBER(A2:A21),A2:A21,””))

   Reply

2. Hello
   I’m looking at results from the recent general election in the UK. As raw data I have the complete results in one spread sheet.
   Using a simple Pivot Table I can display the results of each parliamentary seat. Some seats have more candidates than others – but no problem.
   The election produces 650 “winners” and the PT displays the winning Party for each seat.
   .
   I wish to asign a weighting to each political party in terms of how far right or how far left I perceive each to be so that I can make a guesstimate of how each seat would have faired under a different voting system.
   Should be easy! Use VLOOKUP to each PV result and use the weighting.
   The issue is that to take one seat at randon: There may be six candidates from over the 100 parties. When I look up the result there will be over 100 results the vast majority being N/As.

   Is it possible to, rather than use IFERROR and then sort by value, force the VLOOKUP to “skip” each N/A and only return the valid results?

   Easier to provide spreadsheet than describe issue!

   Reply

   1. Hi John…To handle the situation where you have multiple candidates per seat and want to avoid displaying `N/A` results while using `VLOOKUP`, you can use a combination of Excel functions to filter out the `N/A` values. Here’s how you can achieve this:

      ### Step-by-Step Solution

      1. **Assign Weightings**:
      – Create a table with political parties and their assigned weightings.

      2. **Use INDEX and MATCH Instead of VLOOKUP**:
      – INDEX and MATCH can be more flexible and powerful compared to VLOOKUP for this type of task.

      3. **Filter Out N/A Values**:
      – Use the `IFERROR` function in combination with `INDEX` and `MATCH` to skip over `N/A` values.

      Here’s a simplified example of how you can set this up:

      ### Example Data

      **Sheet 1 (Election Results):**
      | Seat | Candidate | Party |
      |——–|————|———-|
      | Seat 1 | Candidate A| Party X |
      | Seat 1 | Candidate B| Party Y |
      | Seat 1 | Candidate C| Party Z |
      | Seat 2 | Candidate D| Party X |
      | Seat 2 | Candidate E| Party Y |
      | Seat 3 | Candidate F| Party Z |
      | Seat 3 | Candidate G| Party X |
      | Seat 3 | Candidate H| Party Y |

      **Sheet 2 (Party Weightings):**
      | Party | Weighting |
      |———|———–|
      | Party X | 1 |
      | Party Y | -1 |
      | Party Z | 0.5 |

      ### Using INDEX, MATCH, and IFERROR

      1. **Create a Formula to Fetch Weightings:**
      – In the election results sheet, create a column to fetch the weightings of each party.

      Example Formula in D2 (assuming your data starts from row 2):
      “`excel
      =IFERROR(INDEX(Sheet2!$B$2:$B$100, MATCH(C2, Sheet2!$A$2:$A$100, 0)), “”)
      “`

      2. **Aggregating Weightings for Each Seat:**
      – Use a Pivot Table to summarize the weightings for each seat.

      ### Steps to Set Up in Excel:

      1. **Insert a New Column in the Election Results Sheet:**
      – Label it “Weighting”.
      – Use the formula above to fill this column with the corresponding weightings for each candidate’s party.

      2. **Create a Pivot Table:**
      – Insert a Pivot Table with “Seat” as the row label.
      – Add the “Weighting” column as a value and set it to “Sum”.

      ### Example:

      #### Election Results Sheet:
      | Seat | Candidate | Party | Weighting |
      |——–|————|———-|———–|
      | Seat 1 | Candidate A| Party X | 1 |
      | Seat 1 | Candidate B| Party Y | -1 |
      | Seat 1 | Candidate C| Party Z | 0.5 |
      | Seat 2 | Candidate D| Party X | 1 |
      | Seat 2 | Candidate E| Party Y | -1 |
      | Seat 3 | Candidate F| Party Z | 0.5 |
      | Seat 3 | Candidate G| Party X | 1 |
      | Seat 3 | Candidate H| Party Y | -1 |

      #### Pivot Table:
      | Seat | Sum of Weighting |
      |——–|——————|
      | Seat 1 | 0.5 |
      | Seat 2 | 0 |
      | Seat 3 | 0.5 |

      By using this method, you can skip over `N/A` values and get a clear view of the weightings for each seat based on the different candidates and their parties. If you need further customization or face any specific issues with your data, sharing the spreadsheet would indeed make it easier to provide more precise assistance.

      Reply