Gaurav Chauhan
Gaurav Chauhan ✭✭✭✭✭✭


How do I write a IFERROR AVG formulae wherein I have 19 sets of KPIs each with Goal Value and Goal Achieved value. It would be common that not all 19 are applicable so users can leave them blank or enter NA or type in anything versus to leave them blank in either Goal Value or Goal Achieved value.

How shall I achieve it. thanks.


  • L_123
    L_123 ✭✭✭✭✭✭

    instead of iferror avg, try =avg(collect())

    not knowing what you are looking for for data, I can't tell you specifically what to use as the criteria, but you could use isnumber(@cell) or istext(@cell) with any combination of conditionals that you want /criteria.

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    Thank for your response.

    Please see the below table. The data or sheet is prepared as below. Just need the average for each to be calculated

    AVG ( Goal1/Achieved1, Goal2/ Achieved2, ........)

    But with the logics that if a particular goal and Achieved are left blanks, or Zero entered in both or NA entered in both, it should still give out a Avg value vs throwing an error.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/16/22

    ok, I misunderstood then. for this one it's probably better to break it down into parts.

    =avg(iferror(sum([goal 1]@row)/sum([achieved 1]@row),""),iferror(sum([goal 2]@row)/sum([achieved 2]@row),""

    sum, while not actually "summing" the cell, will post a 0 if something other than an integer is found, and therefore can be used for some simple text parsing in this case. iferror is to get rid of the /0 error. "" is to not count the errors into the average for the results of the formula

    @Paul Newcome Maybe you can come up with a cleaner formula than this, I feel like I might be missing something.

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    Thank you. I was also provided with another formulae but with 19 KPIs its difficult to write this. Any direction or confirmation will help.

    =IFERROR(IFERROR(AVG(([Achieved 1]@row / [Goal 1]@row), ([Achieved 2]@row / [Goal 2]@row)), AVG([Achieved 1]@row / [Goal 1]@row)), "")

    The first IFERROR prevents a blank Goal 1 throwing up an error, the second calculates Goal 1 alone if values are missing in Goal 2.

  • L_123
    L_123 ✭✭✭✭✭✭

    If it is to long, then try using

    =iferror(sum([goal 1]@row)/sum([achieved 1]@row),"")

    in a helper column for each, then averaging the resulting helpers. You can hide the helpers out so you don't have to see them after

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I personally would go with the (hidden) helper columns. Not only will it help you manage the data more effectively in the sheet, but it will also give you the ability to report on each separately using a metrics sheet (if needed).