# Receiving #INVALID OPERATOR when using AVERAGEIF

✭✭

Hello,

I'm trying to average the value of Opportunity Age (Days) in a cell when Sales Stage contains "Won".

I'm receiving an #INVALID OPERATOR for the following:

=AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], (CONTAINS("Won", @cell)))

Any tips you could provide would be greatly appreciated - thank you.

• ✭✭✭✭✭✭

I am not sure why the AVERAGEIF wasn't working. The AVG/COLLECT was going to be my next idea if we were unable to get your original squared away.

• ✭✭

Sorry - #INVALID OPERATION

• ✭✭✭✭✭✭

The only thing I can see right off is a set of parenthesis that isn't needed. Does it work if we remove those as below?

=AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], CONTAINS("Won", @cell))

• ✭✭

Hi Paul,

I tried removing the Paranthesis - but still shows as #INVALID OPERATION.

Would i be better off trying to use Collect?

Here is an image:

• ✭✭✭✭✭✭

May be this?

=AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], @cell = "Won")

Is that particular error present in either of the two columns being evaluated?

• ✭✭

Hi Paul,

Still getting #INVALID OPERATION for

=AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], @cell = "Won")

The formula is in an added field in the Sheet Summary - So not sure if error is present in either or both columns.

thanks

• ✭✭✭✭✭✭

Double check the columns. If that error is present in even one cell being referenced by the formula, it will pull through.

• ✭✭

Hi paul, thank you for all the help - the image below is what I'm looking at. Forgive my ignorance - how would i determine if the error is present in even one cell of those columns being referenced?

• ✭✭✭✭✭✭

The error would be visible the same way it is visible in the sheet summary field, but I don't see it in any cells in your screenshot.

Try deleting the formula and the Sheet Summary field then creating a new field and manually typing the formula back in.

• ✭✭

Hi Paul - I went ahead and deleted the formula and the Sheet Summary Field then I created a new field and manually typed the formula back in. I received the same error #INVALID OPERATION.

However, I decided to try this in the new field:

=AVG(COLLECT([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], "Won"))

and the formula worked like a charm!

I have no idea why

=AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], @cell = "Won")

is still returning #INVALID OPERATION,

but I'll go with =AVG(COLLECT([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], "Won")) for the Win!

Thank you for all your help! I'm really puzzled why the original =AVERAGEIF didn't work.

• ✭✭✭✭✭✭