Can you add a nested IF formula to AVGW (weighted average)?

I am trying to find the weighted average if the the RAID column is Risk or Issue and/or if the Status column is Not Started or In Progress

Is that possible with the formula =avgw?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Melissa Torrez

    Yes! Instead of using an IF statement, you could use the COLLECT Function to filter down the rows you want to assess.

    You'll need to use COLLECT for both the "Range" and the "Range Weight" to filter both columns based on your criteria. Ex:

    =AVGW(COLLECT(formula), COLLECT(formula))

    With a COLLECT function, the first column you list is the one you want to look at in your AVGW. Then you list each of the columns with criteria afterwards, like so:

    COLLECT([Column to Avg]:[Column to Avg], RAID:RAID, OR(@cell = "Risk", @cell = "Issue"), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")

    So in your formula, something like this:

    =AVGW(COLLECT([Column to Avg]:[Column to Avg], RAID:RAID, OR(@cell = "Risk", @cell = "Issue"), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")), COLLECT([Range Weight]:[Range Weight], RAID:RAID, OR(@cell = "Risk", @cell = "Issue"), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")))


    Let me know if that makes sense and works for you.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

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

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Melissa Torrez

    Yes! Instead of using an IF statement, you could use the COLLECT Function to filter down the rows you want to assess.

    You'll need to use COLLECT for both the "Range" and the "Range Weight" to filter both columns based on your criteria. Ex:

    =AVGW(COLLECT(formula), COLLECT(formula))

    With a COLLECT function, the first column you list is the one you want to look at in your AVGW. Then you list each of the columns with criteria afterwards, like so:

    COLLECT([Column to Avg]:[Column to Avg], RAID:RAID, OR(@cell = "Risk", @cell = "Issue"), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")

    So in your formula, something like this:

    =AVGW(COLLECT([Column to Avg]:[Column to Avg], RAID:RAID, OR(@cell = "Risk", @cell = "Issue"), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")), COLLECT([Range Weight]:[Range Weight], RAID:RAID, OR(@cell = "Risk", @cell = "Issue"), Status:Status, OR(@cell = "Not Started", @cell = "In Progress")))


    Let me know if that makes sense and works for you.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

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

  • Melissa Torrez
    Melissa Torrez ✭✭✭✭✭

    Hi @Genevieve P. this worked beautifully! I would have never thought to use the collect function. Thank you so much.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!