Help with AverageIF/Collect formula

mproudfit

Hey there! Long time listener, first time caller. Im looking at needing some assistance with multiple criteria formula to pull in average costs for construction projects based on PROJECT TYPE and PROJECT STATUS. My desired end result is to view the AVG cost on a project type basis for all projects that fall under green, yellow, gray color coding. Im currently rocking the below formula but am getting slapped with an unparseable response. Any help would be appreciated!

=AVERAGEIF(COLLECT([$/SF]1[$/SF]50:TYPE50,TYPE1:TYPE50,"PROTO",STATUS1:STATUS50,"GREEN",STATUS1:STATUS50,"YELLOW",STATUS1:STATUS50,"GRAY")

Paul Newcome

Try this...

=AVG(COLLECT([$/SF]1:[$/SF]50, Type1:Type50, "Proto", Status1:Status50, AND(@cell <> "", @cell <> "Red"))

Paul Newcome

Try this...

=AVG(COLLECT([$/SF]1:[$/SF]50, Type1:Type50, "Proto", Status1:Status50, AND(@cell <> "", @cell <> "Red"))

mproudfit

Thank you - that worked. Would you mind explaining what the tail end of that formula is doing? Ive never seen/used @cell references like that before. Are you able to piece together why my original formula didnt work?

Paul Newcome

@cell basically tells the function to evaluate the range on a cell by cell basis. <> is saying "not equal to", and double quotes "" is "blank". So basically we are saying to collect he cells in the specified range where the Status is not blank and is not "Red".

There were a few different problems with your original formula.

=AVERAGEIF(COLLECT([$/SF]1[$/SF]50:TYPE50,TYPE1:TYPE50,"PROTO",STATUS1:STATUS50,"GREEN",STATUS1:STATUS50,"YELLOW",STATUS1:STATUS50,"GRAY")

The AVERAGEIF function already has a single set of range/criteria built in, so the COLLECT function becomes redundant and can actually put everything in the wrong order for the AVERAGEIF if you are not very careful. That is why I went to a basic AVG function with the COLLECT.

Next we can take a look at the ranges themselves. The very first two ranges look like they got a little mixed up when you were entering them.

[$/SF]1[$/SF]50:TYPE50,TYPE1:TYPE50

vs

[$/SF]1:[$/SF]50, Type1:Type50

Next we will take a look at your Status ranges/criteria.

STATUS1:STATUS50,"GREEN",STATUS1:STATUS50,"YELLOW",STATUS1:STATUS50,"GRAY"

The COLLECT function basically has a built in AND. So your formula is essentially saying to pull from the first range based on the status column being "GREEN", "YELLOW" and "GRAY" all at the same time which is not possible.

Also... When referencing a symbol type column, you need to make sure that the text in your formula matches the text for the symbol being referenced which is also case sensitive.

Green

vs

GREEN

mproudfit

Great breakdown. Thank you so much!

Paul Newcome

Happy to help. 👍️