Average IF formulae

Gaurav Chauhan
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.

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    Can you share the formula you are using?

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

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

  • Nick Korna
    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
    Gaurav Chauhan ✭✭✭✭✭✭
    edited 08/15/22

    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