How to find average considering multiple criteria?

Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Options

This helped! thank you!

• Employee
Options

No problem! 😊

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
edited 06/21/22
Options

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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!