How to find average considering multiple criteria?

Garrett Ricker
Garrett Ricker ✭✭
in Formulas and Functions

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))
· Share on FacebookShare on Twitter

Best Answer

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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Answers

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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Garrett Ricker
    Garrett Ricker ✭✭

    This helped! thank you!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    No problem! 😊

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • 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?

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!