#1
|
|||
|
|||
Counting Number of Months from a static date
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. |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
Ah, that's so easy you're going to kick yourself:
Code:
=SUMIF(Sheet2!A:A,B3,Sheet2!B:B)/Sheet2!H1 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. Last edited by BobBridges; 09-04-2013 at 06:51 AM. Reason: Typo |
#5
|
|||
|
|||
This got pushed to the way way way back burner, but I finally tested it out and it works. Thank you.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Number and date formatting | dmarie123 | Word VBA | 5 | 02-28-2013 03:10 PM |
Converting serial number to years, months and days | hannu | Excel | 6 | 02-14-2013 09:21 PM |
Adding months to a Date | tsindos | Word | 4 | 10-04-2011 11:20 PM |
Setting up recurring months to skip some months etc. | dwelch@ykfireprevention.c | Outlook | 0 | 11-30-2010 10:15 AM |
Help needed using the serial number date with sumifs - whole office is stumped | FraserKitchell | Excel | 3 | 01-06-2010 12:24 PM |