Multipart Average Question (With Criteria)

Courtney Caswell

I am struggling to put together a multiple part formula that only does the average of a column if certain factors are met.

I want to take the average number in column [NetDays] only if done is check and the row in column [Event Location] includes the word ABC. If possible only for events within the last 365 days

This is what I originally tried which is #INVALID DATA TYPE

=AVERAGEIF(NetDays:NetDays, AND(Done:Done = 1, [Event Location]:[Event Location] = CONTAINS("ABC")))

I also tried to mess around with =AVG(IF(AND(OR) combos

Can someone help me?

L_123

That means there arent any values found by the collect formula. Do you have any matching values where the done column contains a 1 and the event location column contains "ABC"?

L_123

Nice! That is almost exactly what I would have recommended :)

The only recommendation I would make (and it is a small one) is to add the @cell in your criteria for your date. It is a good habit to get into for the collect formula as using it can stop some headaches in troubleshooting/adding to the formulas in the future

=AVG(COLLECT(NetDays:NetDays, Done:Done, @cell = 1, [Event Location]:[Event Location], CONTAINS("ABC", @cell), [Final Letter]:[Final Letter],@cell >=TODAY(-365)))

L_123

=avg(collect(netdays:netdays,done:done,@cell = 1, [event location]:[event location],contains("ABC",@cell

Give that a try

Courtney Caswell

@L@123 I am now receiving a response that says "#Divide by zero"

L_123

That means there arent any values found by the collect formula. Do you have any matching values where the done column contains a 1 and the event location column contains "ABC"?

Courtney Caswell

@L@123 I essentially have 4 options. ABC_123, BCA_123, ABC, BCA. When I removed the underscore and just did a space it looks like it corrected itself. Thank you so much! Now I just need to find out how to only look at the last 365 per a different row.

L_123

The last 365 days? Or last 365 values?

Courtney Caswell

@L@123 the last 365 days. I believe I figured it out after a lot of messing around (and missing plenty of ,)

=AVG(COLLECT(NetDays:NetDays, Done:Done, @cell = 1, [Event Location]:[Event Location], CONTAINS("ABC", @cell), [Final Letter]:[Final Letter], >=TODAY(-365)))

L_123

Nice! That is almost exactly what I would have recommended :)

The only recommendation I would make (and it is a small one) is to add the @cell in your criteria for your date. It is a good habit to get into for the collect formula as using it can stop some headaches in troubleshooting/adding to the formulas in the future

=AVG(COLLECT(NetDays:NetDays, Done:Done, @cell = 1, [Event Location]:[Event Location], CONTAINS("ABC", @cell), [Final Letter]:[Final Letter],@cell >=TODAY(-365)))