How to find average considering multiple criteria?

Garrett Ricker

Looking to find the average margin(%) of transportation lanes which have a Transportation Solution that is "Full Truckload" and the rate quote is "Approved". i have tried several different formulas shown below. Disclaimer, not a formula expert.

1. =IF(AND({Leg 1 Sales Quote Tracker Capture Form Range 4} = "Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 21} = "Approved"), AVG({Leg 1 Sales Quote Tracker Capture Form Range 29}), IF(AND({Leg 1 Sales Quote Tracker Capture Form Range 4} = "Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 22} = "Approved"), AVG({Leg 1 Sales Quote Tracker Capture Form Range 30})))
2. =IF(AND({Leg 1 Sales Quote Tracker Capture Form Range 4} = "Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 22} = "Approved"), AVERAGEIF({Leg 1 Sales Quote Tracker Capture Form Range 30} > 0), IF(AND({Leg 1 Sales Quote Tracker Capture Form Range 4} = "Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 21} = "Approved"), AVERAGEIF({Leg 1 Sales Quote Tracker Capture Form Range 29} > 0)))
3. =IF(AND({Leg 1 Sales Quote Tracker Capture Form Range 4} = "Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 21} = "Approved"), AVERAGEIF({Leg 1 Sales Quote Tracker Capture Form Range 31},>),IF(AND({Leg 1 Sales Quote Tracker Capture Form Range 4}="Full Truckload",{Leg 1 Sales Quote Tracker Capture Form Range 22}="Approved"),AVERAGEIF({Leg 1 Sales Quote Tracker Capture Form Range 15},>0)))
4. =AVG(COLLECT({Leg 1 Sales Quote Tracker Capture Form Range 31}, {Leg 1 Sales Quote Tracker Capture Form Range 4}, ="Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 21},="Approved))

Genevieve P.

Hi @Garrett Ricker

You're very close with your last formula!

To specify multiple criteria for an AVG formula, you'll want to use AVG(COLLECT.

An AVG(COLLECT works like this:

=AVG(COLLECT({Column to Avg}, {First Column}, "First Criteria", {Second Column}, "Second Criteria"))

In your final formula, it looks like it might just be missing a closing quote around "Approved"... try this:

=AVG(COLLECT({Leg 1 Sales Quote Tracker Capture Form Range 31}, {Leg 1 Sales Quote Tracker Capture Form Range 4}, "Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 21}, "Approved"))

Note that the first range (Range 31) should just be the margin(%) column. Let me know if this works.

Cheers!

Genevieve

Genevieve P.

Hi @Garrett Ricker

You're very close with your last formula!

To specify multiple criteria for an AVG formula, you'll want to use AVG(COLLECT.

An AVG(COLLECT works like this:

=AVG(COLLECT({Column to Avg}, {First Column}, "First Criteria", {Second Column}, "Second Criteria"))

In your final formula, it looks like it might just be missing a closing quote around "Approved"... try this:

=AVG(COLLECT({Leg 1 Sales Quote Tracker Capture Form Range 31}, {Leg 1 Sales Quote Tracker Capture Form Range 4}, "Full Truckload", {Leg 1 Sales Quote Tracker Capture Form Range 21}, "Approved"))

Note that the first range (Range 31) should just be the margin(%) column. Let me know if this works.

Cheers!

Genevieve

Garrett Ricker

This helped! thank you!

Genevieve P.

No problem! 😊

Claire Holt

Hello,

This is very useful, it would be great if you could please help me add in NOT as a function? I need to collect average data but not if another column equals a particular assay type. How could I use something similar to this but collecting the average values.

=COUNTIFS({In vitro requests Range 2}, $Status2, {In vitro requests Range 3}, NOT(OR(@cell = $Referencs@row, @cell = $Referencs3, @cell = $Referencs4, @cell = $Referencs5)))

Any help greatly received, many thanks

Genevieve P.

Hi @Claire Holt

I find it easier to use <> to say "not" so I don't have to think about parentheses. You'll also want to use AND instead of OR.

Try something like this:

=COUNTIFS({In vitro requests Range 2}, $Status2, {In vitro requests Range 3}, AND(@cell <> $Referencs@row, @cell <> $Referencs3, @cell <> $Referencs4, @cell <> $Referencs5))

Cheers,

Genevieve

Claire Holt

Amazing!! Thank you so much

Just realised this hasn't worked if I wanted the average of the numbers instead of counting.

Is this something I am able to do?