IFERROR AVG

Gaurav Chauhan

Hi,

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

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

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

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

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

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

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).