Provide the average of column P when a number in A2 and a date in B2 are the same ?

joeydeacan

New Member
Joined
Mar 26, 2014
Messages
6
I wondering if this can even be done, Ive been pulling my hair out, Ive got 700 lines of data and have started doing this manually and im not even on three figures yet.

I have done column P manually so you can undertsand my explanation.

Any help greatly , and I mean very gratefully receievd.

excel pic 1.JPG
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,941
Office Version
  1. 365
Platform
  1. Windows
Just use the AVERAGEIF function.

Put this in P2 and copy down to P7:
Excel Formula:
=AVERAGEIF($A$2:$A$7,A2,$O$2:$O$7)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,053
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
ABNOP
1
281001/08/20209.879.87
380003/08/202019.749.975
480003/08/20200.219.975
580503/08/20209.034.62
680503/08/20200.214.62
780803/08/202021.4221.42
Master
Cell Formulas
RangeFormula
P2:P7P2=AVERAGEIFS(O:O,A:A,A2,B:B,B2)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,941
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Note: I was working off the first image, which only considers column A.
Fluff's worked off the second image, which considers column A and B. So I am guessing that is the formula you want.
 
Last edited:

joeydeacan

New Member
Joined
Mar 26, 2014
Messages
6
Works fine with a small number, but is there a way of including the date ? For example I will have another set that have 800 in column A, but the date will be 04/08/2020.

So if A2 and A3 are the same AND B2 and B3 are the same, im not sure im explaining very well.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,053
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

See post#4 ;)
 

joeydeacan

New Member
Joined
Mar 26, 2014
Messages
6
You guys !!!!! You guys.

I love you guys, it worked, its brilliant, im shocked and in awe !!!!

If you would both like to set a charity Id like to make a small donation on your behalf as a mark of thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,941
Office Version
  1. 365
Platform
  1. Windows
Glad we were able to help! :)

If you would both like to set a charity Id like to make a small donation on your behalf as a mark of thanks
I usually tell people just to pay it forward!

If you really want to make a donation, I would just say donate to some local charity in your area that is helping those with the Pandemic, whether it be first responders, or those providing support to those who may have lost their jobs.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,798
Messages
5,598,112
Members
414,214
Latest member
marketingnumbersguy

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