Forum Discussion

tsowards's avatar
tsowards
Copper Contributor
Jan 15, 2020

Weighted averages that omit columns with N/A

I'm trying to create a formula that will calculated weighted averages but not consider N/A as 0 (zero). Please help!

excel
Formulas and Functions

5 Replies

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Jan 15, 2020

    Hello tsowards,

     

    You may use the following formula to average while ignoring errors:

    =AGGREGATE(1,6,Your_Range)

    • tsowards's avatar
      tsowards
      Copper Contributor
      Jan 16, 2020

      Thanks, PReagan but that didn't work for me. I want the Overall Rating to reflect an average that doesn't penalize individuals that do not have a score in a certain Performance Review category (i.e. 'Key Responsibility' may not apply to a position, so they only score themselves under 'Competencies') similar to a student missing an assignment and having a doctor's excuse or other pass to excuse the assignment.

      • PReagan's avatar
        PReagan
        Bronze Contributor
        Jan 17, 2020

        tsowards 

         

        Assuming the title "Overall Rating" is located in cell A2, then cell A3 could be:

        =SUMPRODUCT(B3:F3,$B$1:$F$1)/SUMPRODUCT(--(B3:F3<>"N/A"),$B$1:$F$1)

  • PascalKTeam's avatar
    PascalKTeam
    Iron Contributor
    Jan 15, 2020
    Can you provide an example with actual figures and the desired result please
    • tsowards's avatar
      tsowards
      Copper Contributor
      Jan 16, 2020

      PascalKTeam I want the Overall Rating to reflect an average that doesn't penalize individuals that do not have a score in a certain Performance Review category (i.e. 'Key Responsibility' may not apply to a position, so they only score themselves under 'Competencies') similar to a student missing an assignment and having a doctor's excuse or other pass to excuse the assignment.

Resources