Array Formula With Sumproduct for Average

S

scottlockhart

New Member
Joined
Feb 24, 2016
Messages
8
I have two questions. First, I'm using an array formula in my last column (H) to average the percentages in column B, based on the day in column C. (the data is truncated FIY)
Ex: {=AVERAGE(IF$C$5:$C34=E5,$B$5:$B$34))}

My first question is: how do I embed SUMPRODUCT into the formula (replacing AVERAGE I assume) to base the averages from column B off of the corresponding weights from column A?

Second question: My Day 0 array formula is not working properly. I'm assuming it has to do with the "0", but the AVERAGE function shouldn't be picking up blank cells anyway, should it? Based on the data it should be 100+100+100+100+61.11+27.27+60.00+11.11/8 which equals 69.94%, not 9.07%.

I appreciate anyone's help!
2100.00%
2100.00%0Day 09.07%
1100.00%Day 110.10%
1100.00%0Day 2#DIV/0!
3100.00%Day 3#DIV/0!
3100.00%0Day 4#DIV/0!
4100.00%Day 5#DIV/0!
4100.00%0Day 6#DIV/0!
18100.00%Day 7#DIV/0!
1161.11%0Day 8#DIV/0!
15.56%20Day 9#DIV/0!
15.56%68Day 10#DIV/0!
15.56%99Day 11#DIV/0!
15.56%118Day 12#DIV/0!
15.56%178Day 13#DIV/0!
15.56%188Day 14#DIV/0!
15.56%194Day 15#DIV/0!
11100.00%Day 16#DIV/0!
327.27%0Day 17#DIV/0!
19.09%1Day 18#DIV/0!
19.09%30Day 19#DIV/0!
19.09%58Day 205.56%
19.09%60Day 21#DIV/0!
19.09%91Day 22#DIV/0!
19.09%121Day 23#DIV/0!
19.09%152Day 24#DIV/0!
19.09%183Day 25#DIV/0!
5100.00%Day 26#DIV/0!
360.00%0Day 27#DIV/0!
120.00%30Day 28#DIV/0!
120.00%60Day 29#DIV/0!
9100.00%Day 3014.55%
888.89%0
111.11%1

<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="4" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="4" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="4" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
It's hard to interpret the exhibit. What is the range? What is input and what is output in this data exhibit?
 
Last edited:
Upvote 0
Is this what you are trying to do?
Not sure how your columns line up so you will need to change ranges to match your data.
I also assume column C in the example is numeric.
In your example shouldn't the last number be .8889 and not .1111?

Copy formulas down as needed.
Excel Workbook
ABCDEFGH
12100.00%AverageWt. Average
22100.00%0Day 079.66%75.58%
31100.00%Day 110.10%10.10%
41100.00%0Day 2#DIV/0!#DIV/0!
53100.00%Day 3#DIV/0!#DIV/0!
63100.00%0Day 4#DIV/0!#DIV/0!
74100.00%Day 5#DIV/0!#DIV/0!
84100.00%0Day 6#DIV/0!#DIV/0!
918100.00%Day 7#DIV/0!#DIV/0!
101161.11%0Day 8#DIV/0!#DIV/0!
1115.56%20Day 9#DIV/0!#DIV/0!
1215.56%68Day 10#DIV/0!#DIV/0!
1315.56%99Day 11#DIV/0!#DIV/0!
1415.56%118Day 12#DIV/0!#DIV/0!
1515.56%178Day 13#DIV/0!#DIV/0!
1615.56%188Day 14#DIV/0!#DIV/0!
1715.56%194Day 15#DIV/0!#DIV/0!
1811100.00%Day 16#DIV/0!#DIV/0!
19327.27%0Day 17#DIV/0!#DIV/0!
2019.09%1Day 18#DIV/0!#DIV/0!
2119.09%30Day 19#DIV/0!#DIV/0!
2219.09%58Day 205.56%5.56%
2319.09%60Day 21#DIV/0!#DIV/0!
2419.09%91Day 22#DIV/0!#DIV/0!
2519.09%121Day 23#DIV/0!#DIV/0!
2619.09%152Day 24#DIV/0!#DIV/0!
2719.09%183Day 25#DIV/0!#DIV/0!
285100.00%Day 26#DIV/0!#DIV/0!
29360.00%0Day 27#DIV/0!#DIV/0!
30120.00%30Day 28#DIV/0!#DIV/0!
31120.00%60Day 29#DIV/0!#DIV/0!
329100.00%Day 3014.55%14.55%
33888.89%0
34111.11%1
Sheet
 
Upvote 0
You are correct that the last number is .8889 and not .1111, that was my mistake.

Sorry if this is confusing it's difficult without uploading a spreadsheet! I will test out these formulas and let you know if I have any success. Thanks!

AhoyNC said:
Is this what you are trying to do?
Not sure how your columns line up so you will need to change ranges to match your data.
I also assume column C in the example is numeric.
In your example shouldn't the last number be .8889 and not .1111?

Copy formulas down as needed.

*ABCDEFGH
12100.00%****AverageWt. Average
22100.00%0**Day 079.66%75.58%
31100.00%***Day 110.10%10.10%
41100.00%0**Day 2#DIV/0!#DIV/0!
53100.00%***Day 3#DIV/0!#DIV/0!
63100.00%0**Day 4#DIV/0!#DIV/0!
74100.00%***Day 5#DIV/0!#DIV/0!
84100.00%0**Day 6#DIV/0!#DIV/0!
918100.00%***Day 7#DIV/0!#DIV/0!
101161.11%0**Day 8#DIV/0!#DIV/0!
1115.56%20**Day 9#DIV/0!#DIV/0!
1215.56%68**Day 10#DIV/0!#DIV/0!
1315.56%99**Day 11#DIV/0!#DIV/0!
1415.56%118**Day 12#DIV/0!#DIV/0!
1515.56%178**Day 13#DIV/0!#DIV/0!
1615.56%188**Day 14#DIV/0!#DIV/0!
1715.56%194**Day 15#DIV/0!#DIV/0!
1811100.00%***Day 16#DIV/0!#DIV/0!
19327.27%0**Day 17#DIV/0!#DIV/0!
2019.09%1**Day 18#DIV/0!#DIV/0!
2119.09%30**Day 19#DIV/0!#DIV/0!
2219.09%58**Day 205.56%5.56%
2319.09%60**Day 21#DIV/0!#DIV/0!
2419.09%91**Day 22#DIV/0!#DIV/0!
2519.09%121**Day 23#DIV/0!#DIV/0!
2619.09%152**Day 24#DIV/0!#DIV/0!
2719.09%183**Day 25#DIV/0!#DIV/0!
285100.00%***Day 26#DIV/0!#DIV/0!
29360.00%0**Day 27#DIV/0!#DIV/0!
30120.00%30**Day 28#DIV/0!#DIV/0!
31120.00%60**Day 29#DIV/0!#DIV/0!
329100.00%***Day 3014.55%14.55%
33888.89%0*****
34111.11%1*****

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:44px;"><col style="width:33px;"><col style="width:64px;"><col style="width:84px;"><col style="width:107px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G2{=AVERAGE(IF($C$1:$C$34<>"",IF($C$1:$C$34=MID(F2,FIND("y",F2)+1,5)*1,$B$1:$B$34)))}
H2=SUMPRODUCT(--($C$1:$C$34=MID(F2,FIND("y",F2)+1,5)*1),--($C$1:$C$34<>""),$A$1:$A$34,$B$1:$B$34)/SUMPRODUCT(--($C$1:$C$34=MID(F2,FIND("y",F2)+1,5)*1),--($C$1:$C$34<>""),$A$1:$A$34)

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
I'm getting the #Value! error but I probably just need to go back and see what I'm putting in wrong. However, is there a simpler way by chance to write the formula if I remove "day" from column F and relocate to column E and just have numbers only in column F? It may help by being able to remove the MID and FIND functions. Thanks!
 
Upvote 0
scottlockhart said:
I'm getting the #Value! error but I probably just need to go back and see what I'm putting in wrong. However, is there a simpler way by chance to write the formula if I remove "day" from column F and relocate to column E and just have numbers only in column F? It may help by being able to remove the MID and FIND functions. Thanks!
Click to expand...

Try to repost the sample. Indeed, "day" hinders rather than eases...
 
Upvote 0
The table AhoyNC created shows it best. I put "day" in column E for the first few to show what I mean. To find weighted average with SUMPRODUCT with just the number in cell F is probably a much simpler equation, and I can easily structure the spreadsheet in that way.


ABCDEFGH
12100.00% AverageWt. Average
22100.00%0 Day 079.66%75.58%
31100.00% Day 110.10%10.10%
41100.00%0 Day 2#DIV/0!#DIV/0!
53100.00% Day 3#DIV/0!#DIV/0!
63100.00%0 Day 4#DIV/0!#DIV/0!
74100.00% Day 5#DIV/0!#DIV/0!
84100.00%0 Day 6#DIV/0!#DIV/0!
918100.00% Day 7#DIV/0!#DIV/0!
101161.11%0 Day 8#DIV/0!#DIV/0!
1115.56%20 Day 9#DIV/0!#DIV/0!
1215.56%68 Day 10#DIV/0!#DIV/0!
1315.56%99 Day 11#DIV/0!#DIV/0!
1415.56%118 Day 12#DIV/0!#DIV/0!
1515.56%178 Day 13#DIV/0!#DIV/0!
1615.56%188 Day 14#DIV/0!#DIV/0!
1715.56%194 Day 15#DIV/0!#DIV/0!
1811100.00% Day 16#DIV/0!#DIV/0!
19327.27%0 Day 17#DIV/0!#DIV/0!
2019.09%1 Day 18#DIV/0!#DIV/0!
2119.09%30 Day 19#DIV/0!#DIV/0!
2219.09%58 Day 205.56%5.56%
2319.09%60 Day 21#DIV/0!#DIV/0!
2419.09%91 Day 22#DIV/0!#DIV/0!
2519.09%121 Day 23#DIV/0!#DIV/0!
2619.09%152 Day 24#DIV/0!#DIV/0!
2719.09%183 Day 25#DIV/0!#DIV/0!
285100.00% Day 26#DIV/0!#DIV/0!
29360.00%0 Day 27#DIV/0!#DIV/0!
30120.00%30 Day 28#DIV/0!#DIV/0!
31120.00%60 Day 29#DIV/0!#DIV/0!
329100.00% Day 3014.55%14.55%
33888.89%0
34111.11%1

<tbody>
</tbody>
 
Upvote 0
What does mean 2 in A1? In F it seems we have day numbers, right? In G we seem to have conditional day averages of B, right? It seems you need daily weighted averages. If so, where are the weights?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
849
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
600
epactheactor
E
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
staticfluids
  • Question
Distribute Number into Several Part (Increment) Based on Other Data (Dynamic Array)
Replies
2
Views
83
staticfluids
staticfluids
V
  • Question
Formula issues to find variations when zero is one reference value
Replies
0
Views
123
vladimiratanasiu
V

Forum statistics

Threads
1,215,352
Messages
6,124,451
Members
449,161
Latest member
NHOJ

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top