I have a spreadsheet to show inventory usage since a location has opened. 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 sum of quantity used (col B). I then need to divide that by the number of months that has based from June 2012 until the month of the current system date.

Attached Files

Book1.xlsx (48.5 KB, 7 views)

Ok, first, to get the total from Sheet2 is simple: Use SUMIF instead of AVERAGEIF.

The number of months from 2012-06 to now: I'm sure there must be a built-in Excel function for that. Let me look... No, apparently not. But this article at Microsoft Support lists a formula that'll do it using the YEAR and MONTH functions.

I was able to find the formula i need on that sheet. Thank you.

THe problem is now how do I divide Sheet 1 Column C by Sheet 2 cell H1?


See attached sample.

Attached Files

Book1.xlsx (49.3 KB, 11 views)

Ah, that's so easy you're going to kick yourself:Any time you're not sure of the syntax of the addressing to another location (another sheet, in this case, or even another workbook), you can get Excel to tell you. Here's one way:
1) Select Sheet1 C3, which already has the SUMIF function laid out.
2) Hit <F2> to start editing the formula.
3) Type in the '/' for division. Do not hit <Enter> yet.
4) Navigate to Sheet2 and select H1. Note that Excel has now added the address for that cell to the formula bar.
5) Hit <Enter>.

Now you can copy the new formula to the rest of the cells on Sheet1!C:C. Oh, wait, your worksheet already does that; why? And I see I should have specified ".../Sheet2!H$1". Anyway, you can use that method to see how Excel handles lookups not only on other worksheets but on external workbooks as well.

This got pushed to the way way way back burner, but I finally tested it out and it works. Thank you.