Constant Weighted Average with IF Function.

Assume I have columns C1:C4 as
A
B
B
B

And corresponding percentages as columns D1:D4
75%
89%
70%
96%

Is there a way that I can use the IF function with an array of some sort to make the averages of all the percentages corresponding with "B" be weighted 100% and all corresponding with "A" only be worth 73%?

I feel like I've done things like this before, but it's never been two constant weights that are not defined on my spreadsheet anywhere. I think it's defining them in the function that's throwing me off.

Thanks!

Originally Posted by rylock

Assume I have columns C1:C4 as
A
B
B
B

And corresponding percentages as columns D1:D4
75%
89%
70%
96%

Is there a way that I can use the IF function with an array of some sort to make the averages of all the percentages corresponding with "B" be weighted 100% and all corresponding with "A" only be worth 73%?

I feel like I've done things like this before, but it's never been two constant weights that are not defined on my spreadsheet anywhere. I think it's defining them in the function that's throwing me off.

Thanks!

Hi,
try

Please Login or Register  to view this content.

Entered as an arry formula with Ctrl + Shift + Enter

( replace semicoloms with commas if needed)

Originally Posted by arthurbr

Hi,
try

Please Login or Register  to view this content.

Entered as an arry formula with Ctrl + Shift + Enter

( replace semicoloms with commas if needed)

Hi. Thanks a lot!

I think it's very close, but it's not exactly there yet, because when I end up averaging them out with multiplying the "A's" by .73, the overall average for all the numbers end up lower than any of the data given. It's just supposed to be 27% skewed more towards any percentage with a "B".

The overall average, without weighting, is 83%. When A's are weighted at 73%, the weighted average is 0.774375 or 77.44%. Since the values for the A's are reduced due to weighting, the weighted average will be lower than the overall average before weighting. So this seems fine. Are you expecting something different?

Maybe I'm confusing some of the terminology. I've used weighted averages before (all using the SUMPRODUCT() function) and it's all worked out the way I wanted it to, which was:

Take the example from above.
If you average out the three "B's" (89%, 70%, and 96%) all weighted equally at 100%, you get 85%. So, since the only value left is the "A" at 75% is weighted at 73% instead of 100%, I assumed that the value for the 75% would not hold as much weight as the "B's", so that the average would be in between 82.5 (the average if all of them weighted equally) and 85 (the average of only the "B's"). The main thing, I think, was that I just want the "A" to be less important (by 27%) than the "B's."

I obviously trust all of you way more than me, so I'm sure whatever formula I was given is exactly what I was asking for, but I think I might have misstated what I was intending.

Am I wrong?

Is there anyway that someone can help me with the question that I meant to ask?

I may have misstated what I intended to ask in the beginning of this thread.

(You can read what I meant to say above.)

Thanks!

(Sorry for all th replies to myself, but I keep figuring new things out.)

If there were only two cells, this is the weighted average that I was thinking of:

Cells A1:A2 (with "A" being weighted as 100% and "B" being weighted as 73%)
A
B

Cells B1:B2
70%
75%

Easy Formula:
70(1)+75(.73)
---------------- = 72.1%

1.73

Since I need that to be more general over a much higher number of cells, is there a way that I can do that formula with an array, "IF", or maybe a "SUMPRODUCT" function?

Thanks again!

Well if you wish to use arrays, shift ctrl enter, I think the following is what you require


=SUM(IF(A3:A6="b",B3:B6,(B3:B6)*0.73))/SUM(IF(A3:A6="b",1,0.73))

does that help?

Regards


Dav