# Average IF formulae

Options
✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭
Options

Can you share the formula you are using?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
edited 08/15/22
Options

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