Subtract IF

[Bryce Justine]

I'm trying to figure out the last part of this formula.

I have a spreadsheet that flags numbers when they reach 45 as this means the vehicle is at capacity and anything over would be excess.
I have used this formula to find the average with the condition that the number is equal to or greater than 45

=IFERROR(AVERAGEIF(D8:AK8,">=45"),0)

This gives me the number I need there. However, I want to subtract 45 if the number is greater than or equal to 45 to determine what the excess capacity is.
Can this be done in the same formula or do I need to add a separate column?

Cheers

[63falcondude]

Try this:

=IFERROR(AVERAGEIF(D8:AK8,">=45")-45,0)

[Bryce Justine]

@63falcondude That worked thanks!
Was putting the -45 in the wrong spot. Appreciate the help

[63falcondude]

You're welcome. Thanks for the rep!