Average IF formulae

Gaurav Chauhan

Hi @Nick Korna,

I have a scenario here where I have placed a simple Avg formulae and a AvgIF formulae to calculate scenario 1

But incase if there is a Zero, or NA or something, the formulae should be able to still calculate which isnt working with the simple IF or <>0 or If error.

Can you please suggest.

Sameer Karkhanis

Can you share the formula you are using?

Gaurav Chauhan

=AVG([Samples Sent - $ Achieved]@row / [Samples Sent - $ At Risk]@row, [Resource Utilization - $ Achieved]@row / [Resource Utilization - $ At Risk]@row)

Nick Korna

Hi @Gaurav Chauhan,

The below should work for you:

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

Result sample:

Hope this is of assistance!

Gaurav Chauhan

Thank you Nick. Would this formulae work in either case if any of the values between Goal 1 or 2 and Achieved 1 or 2 are left blank or typed NA or n/a

The above mentioned formulae only works with one Goal being blank or NA

Please keep in mind there are 19 Goal and Targets for which I would have to include or extend the formulae. Please help