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

Melissa Torrez

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?

Genevieve P.

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

Genevieve P.

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

Melissa Torrez

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