Receiving #INVALID OPERATOR when using AVERAGEIF

julieg

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.

Paul Newcome

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.

julieg

Sorry - #INVALID OPERATION

Paul Newcome

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))

julieg

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:

Paul Newcome

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?

julieg

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

Paul Newcome

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

julieg

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?

Paul Newcome

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.

julieg

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.

Paul Newcome

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.

julieg

Thanks so much for your help Paul