How to find average considering multiple criteria?

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

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • This helped! thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! 😊

  • Claire Holt
    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.
    Genevieve P. Employee Admin

    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
    Claire Holt ✭✭✭
    edited 06/21/22

    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!