I am trying to create a weighted average by type summary.
How can I use a sumproduct function that has array criteria of each "type" and omits zero amounts? See attached for dataset.
I am trying to create a weighted average by type summary.
How can I use a sumproduct function that has array criteria of each "type" and omits zero amounts? See attached for dataset.
Last edited by thenormanizer; 12-14-2017 at 02:34 AM.
Hi, I'm not sure you have attached the correct file. Do you want to calculate eclectic golf scores for the season?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
For context: Type is Unit type for apartments
Amt is amount rent collected. So 0 means the apartment is vacant
I want to make a summary by type that has the weighted averages with zeros omitted.
Perhaps I'm missing the point, but where are your "weights"?
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
the weight would be the number of units of each type
so amount times the number of units in that unit type is the weighted avg rent
May be try
F2Formula:Please Login or Register to view this content.
G2Formula:Please Login or Register to view this content.
Row\Col A B C D E F G 1 Type Amt 2 1x1-a1 0.001x1-a1 884 3 1x1-a250 887.001x1-a250 881 4 2x2-b250 932.002x2-b250 923 5 2x2-b250 935.001x1-a2 903.5714 6 1x1-a2 930.002x2-b3 973.75 7 1x1-a2 905.002x2-b2 997 8 2x2-b3 897.00 9 2x2-b3 1,014.00 10 1x1-a2 867.00 11 1x1-a2 880.00 12 2x2-b3 953.00 13 2x2-b3 960.00 14 1x1-a1 884.00 15 1x1-a2 0.00 16 2x2-b2 997.00 17 2x2-b250 902.00 18 1x1-a2 884.00 19 1x1-a250 875.00 20 2x2-b3 962.00 21 2x2-b3 1,017.00 22 1x1-a2 932.00 23 1x1-a2 927.00 24 2x2-b3 1,023.00 25 2x2-b3 964.00
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Hi all. Use Ankur's column F formula (Post #6) to get a list of unique types, and perhaps the built-in AVERAGEIFS function instead of SUMPRODUCT in column G:Please Login or Register to view this content.
Last edited by leelnich; 12-14-2017 at 03:02 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks