averageifs?

tly0227

New Member
Joined
Oct 2, 2012
Messages
34
I'm currently going through a data analysis tech bootcamp and am struggling trying to figure out a formula. There's an easier way to get the result I want, but my advisor is encouraging me to figure it out via a formula.


Sheet 1) WTRT
B: # of unitsC: Price per UnitD: Month #
2674.4389.331
2576.1361.951
2131.9845.902
2772.0680.865
2143.4955.966

Sheet 2) EMA
Need a formula that first finds the total cost for each purchase (WTRT B1*C1) and then averages it for each month. The first month # reference is cell C$14 on this sheet. I'm well aware that I could just make a new column and get the totals, and then do an averageifs from there and it'd be a lot easier...but that idea has been nixed.

Is it possible to do this? Can someone please point me in the right direction? I've been messing around with it for a while and am getting nowhere good.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
Hi Tly0227,

Does this do what you want?

WTRT
Book1
BCD
1B: # of unitsC: Price per UnitD: Month #
22674.4389.331
32576.1361.951
42131.9845.92
52772.0680.865
62143.4955.966
WTRT


EMA
Book1
CD
13Month # RefAverage
141199249
15297857.88
163 
174 
185224148.8
196119949.7
EMA
Cell Formulas
RangeFormula
D14:D19D14=IFERROR(SUMPRODUCT((WTRT!$B$2:$B$6*WTRT!$C$2:$C$6)*(WTRT!$D$2:$D$6=C14))/COUNTIF(WTRT!$D$2:$D$6,C14),"")
 

tly0227

New Member
Joined
Oct 2, 2012
Messages
34
I get an error when I try to do that...not sure why. The answer on yours def is what it should be, but I'm not sure iferror is the right formula on my end. The actual sheets will have data for all 12 months...so there's never going to be an instance where there is no average. There's 1460 transaction rows on WTRT. On EMA, the month #s are columns (I don't think that would be effecting why it's not working on my end, but just for more info..)

EMA:
B14C14D14E14F14
Month #
1​
234
Ave. Water Market Balancing Price
Ave. Quantity
=AVERAGEIFS('WTRT'!$B:$B,'WTRT'!$D:$D,'EMA'!C$14)​
=AVERAGEIFS('WTRT'!$B:$B,'WTRT'!$D:$D,'EMA'!D$14)​
=AVERAGEIFS('WTRT'!$B:$B,'WTRT'!$D:$D,'EMA'!E$14)​
=AVERAGEIFS('WTRT'!$B:$B,'WTRT'!$D:$D,'EMA'!F$14)​
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
This should better match your format data

TLY0227.xlsx
BCD
1B: # of unitsC: Price per UnitD: Month #
22674.4389.331
32576.1361.951
42131.9845.92
5123
6124
72772.0680.865
82143.4955.966
WTRT


TLY0227.xlsx
BCDEFGH
14Month #123456
15Ave. Water Market Balancing Price199249.042797857.8822224148.8119949.7
16Ave. Quantity2625.282131.98112772.062143.49
EMA
Cell Formulas
RangeFormula
C15:H15C15=SUMPRODUCT((WTRT!$B$2:$B$1400*WTRT!$C$2:$C$1400)*(WTRT!$D$2:$D$1400=C14))/COUNTIF(WTRT!$D$2:$D$1400,C14)
C16:H16C16=AVERAGEIFS(WTRT!$B:$B,WTRT!$D:$D,EMA!C$14)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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
Top