Microsoft Office Forums Sum Average usage Based on two sheets
 Register FAQ Search Today's Posts Mark Forums Read

#1
09-03-2013, 07:28 AM
 bremen22 Windows 7 64bit Office 2007 Advanced Beginner Join Date: Jul 2013 Posts: 44
Sum Average usage Based on two sheets

I have a work book that looks at usage. There are two sheets. Sheet 1 contains usage data for 1 month. Sheet 2 contains usage data with no restrictions. The data is generated by a data connection to an Oracle database.

Here is what I am trying to accomplish.....

On sheets 1 and 2 there is a Nomenclature Key. For each key that is on sheet 1 there will be a coresponding key on sheet 2. For each time the key appears on sheet 2 I want to get the average of the total quantites. I have attached a small sample of my sheet and what I am trying to accomplish.
Attached Files
 Book1.xlsx (40.3 KB, 7 views)
#2
09-03-2013, 09:59 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 695

So for C3 in your sample, you want a formula that does this:
1) Checks the nomenclature key in B3 (that's 243, in this case)
2) Finds all the rows in Sheet2 that have that nomenclature key in col A
3) Displays the average of the corresponding values in col B

Right? Seems to me you want COUNTIF and SUMIF. No, wait, I just looked it up; Excel now has an AVERAGEIF function, so it's even simpler. I've never used it, but according to the documentation it should go like this:
Code:
`=AVERAGEIF(Sheet2!A:A,B3,Sheet2!B:B)`
Which says "Look in Sheet2 col A for any rows that equal B3, and for those rows return the average of the values in Sheet2 col B".
#3
09-03-2013, 11:00 AM
 bremen22 Windows 7 64bit Office 2007 Advanced Beginner Join Date: Jul 2013 Posts: 44

Thank you very much. The Formula works exactly as I need it to. However, I asked the wrong question.....

Thats how it usually works for me.

There will be a new post with the updated questions.

Thanks again.
#4
09-03-2013, 11:27 AM
 BobBridges Windows 7 64bit Office 2010 32bit Expert Join Date: May 2013 Location: USA Posts: 695

LOL! It often happens that way; once I see the answer, I realize I asked the wrong question. Well, someone will be here.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post blaqsheep Excel 0 02-07-2012 07:04 PM markg2 Excel 2 01-07-2012 05:46 PM waqer Excel 4 09-01-2011 12:35 PM nfphilpot Excel 3 11-24-2010 02:19 PM speedycorn1 Excel 1 10-30-2010 07:54 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:45 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top