IFERROR AVG
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.
Answers
-
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.
-
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.
-
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.
-
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.
-
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
-
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).
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives