Conditional Weighted Average with Multiple Variables

Hey guys,

Spent about 5 hours or more trying to figure this out.. I don't see myself completing the formula without help.

I need the weighted average for [(P) Storeys] where the following criteria are met:

1: [(C) Approved] = "Pending"
2: [(O) Approved] = "Pending"
3: [(O) Approved] ="*Divisional*"

How do I do this?
My closes success, possibly:

=SUMPRODUCT((Master[(P) Storeys])*(Master[(C) Approved]={"Pending"})*(Master[(O) Approved]={"Pending","*Divisional*"}))/SUMPRODUCT(--((Master[(C) Approved]={"Pending"})*(Master[(O) Approved]={"Pending","*Divisional*"})))

.. but it returns as #DIV/0!

Originally Posted by daddylonglegs

Where you have "*Divisional*", are you using the asterisks as wildcards, I'm assuming so.......

I can't see how this is a "weighted average", for that you'd need at least two numeric columns (one for the value and one for the weight). What you are describing seems to be a simple average with conditions. You can do that with an array formula like this:

=AVERAGE(IF(Master[(C) Approved]="Pending",IF((Master[(O) Approved]="Pending")+ISNUMBER(SEARCH("Divisional",Master[(O) Approved])),Master[(P) Storeys])))

Confirm with CTRL+SHIFT+ENTER

Not sure what I'm doing wrong, but this returns as #DIV/0!

Originally Posted by daddylonglegs

So if ([(C) Approved]="Pending" then will that row be included irrespective of the [(O) Approved] value?

Yes. Essentially, average for when [(C) Approved] is "Pending", or [(O) Approved] is "Pending" OR "*Divisional*"

Also, I edited my previous post as well to add:

So, It's kind of like the weighted (?) average of
=AVERAGEIF([(C) Approved], "Pending",[(P) Stories] ) + =AVERAGEIF([(O) Approved], "Pending",[(P) Stories] ) + =AVERAGEIF([(O) Approved], "*Divisional*",[(P) Stories] )

So the weights, I think, come from the fact that each group has a different count... the weight I think would be the count

(Thanks so much btw!)

Originally Posted by daddylonglegs

OK, amending my previous suggestion you can try this array formula

=AVERAGE(IF((Master[(C) Approved]="Pending")+(Master[(O) Approved]="Pending")+ISNUMBER(SEARCH("Divisional",Master[(O) Approved])),Master[(P) Storeys]))

Confirm with CTRL+SHIFT+ENTER

That will average all rows where any of the 3 conditions apply

Does that work for you?

It totally does, wow, thanks!

Now onto problem number 2...

I need the same average for Master[(P) Storeys] where

Master[OMB Approved], "Yes", AND Master[(C) Approved], "<>Yes, via settlement"

I modified the formula you sent, and made this... is it correct?

=AVERAGE(IF((Master[(C) Approved]= "<>Yes, via settlement")+(Master[OMB Approved]="Yes"),Master[(P) Storeys]))

Thanks,

Final problem:


Averaging for..

(Master[(C) Approved]<>"Yes, via settlement")*(Master[OMB Approved]="Yes") OR
(Master[(C) Approved]="Yes, via settlement")*(Master[OMB Approved]="Yes") OR
Master[OMB Approved], "Yes, pending conditions" OR
Master[OMB Approved], "Withdrawn" OR
Master[OMB Approved], "Unclear*"

I can calculate this way as well... if too much hassle to put into one formula. otherway.png

Tried figuring it out.. to difficult for me.

Pretty disappointed, as I tried hiring someone on Upwork to help me finish the set of averaging calculations.

Any suggestions on how I could accomplish the following?

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and (Master[(C) Approved], "Pending" or Master[OMB Approved], "Pending" or Master[OMB Approved], "*Divisional*")

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and (Master[(C) Approved], "Pending"

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and Master[(OZ) Storeys ] > 0 and Master[(F Storeys ] > 0 , and (Master[(C) Approved], "Pending"

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and (Master[(C) Approved]={"Yes","Yes, partially"}))

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and Master [(F Storeys ] > 0 , and (Master[(C) Approved]={"Yes","Yes, partially"}))

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and Master[(OZ) Storeys ] > 0 and Master[(F Storeys ] > 0 , and (Master[(C) Approved]={"Yes","Yes, partially"}))

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and (((Master[OMB Approved], "Yes, pending conditions") OR ((Master[OMB Approved]="Yes") and ((Master[(C) Approved]<>"Yes, via settlement") OR (Master[(C) Approved]="Yes, via settlement"))))

‘= Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and Master[(F Storeys ] > 0 , and (((Master[OMB Approved], "Yes, pending conditions") OR ((Master[OMB Approved]="Yes") and ((Master[(C) Approved]<>"Yes, via settlement") OR (Master[(C) Approved]="Yes, via settlement"))))

‘=Average Master[(P) Storeys] where Master[(P) Hearing Date ] > January 1, 2007 and < December 31, 2007, and Master[(OZ) Storeys ] > 0 and Master[(F Storeys ] > 0 , and (((Master[OMB Approved], "Yes, pending conditions") OR ((Master[OMB Approved]="Yes") and ((Master[(C) Approved]<>"Yes, via settlement") OR (Master[(C) Approved]="Yes, via settlement"))))

Update: All problems solved with Pivot tables