Need to find first and last occurrences in multiple columns to reference adjacent fields

[mwjessica]

Hi All,

I hope someone can help me. I have an Excel spreadsheet that contains records of shipments. Columns include: Order Date, Part Number 1, Part Number 2, Part Number 3, etc.

This is what I'm trying to do: For each Part Number column, I need to automatically find the first and last values, and reference the adjacent Order Dates. A lot of the cells in the Part Number columns are blank, thus my need to find first and last Order Dates based on the first and last occurrence of values in each column. Then I'd like to take those dates and plug them into a simple formula (one formula per column, probably residing above the heading) to calculate the number of months of shipment history.

Currently, I'm using a formula to simply calculate the number of months of ALL order history (see below). But this isn't an accurate number for most part numbers, as different parts started getting ordered on different dates.

MonthsOfHistory = (MAX(ShipmentHistory[Order Date]) - MIN(ShipmentHistory[Order Date])) / 30
AvgMonthlyUsage = ShipmentHistory[[#Totals],[Part_Number_1]] / MonthsOfHistory

What I'm trying to do with this information is calculate average monthly usage per Part Number. Using my crappy, generic formula above gives me numbers that are too low.

I hope this makes sense. Any help or suggestions would be greatly appreciated.

-Jessica

 

[p45cal]

Excel Workbook
	A	B	C	D	E	F	G	H	I
1						Part No.	Most recent	Earliest	difference
2						DD373	28-Mar-11	16-Jan-11	2 months 12 days
3	Order Date	Part No. 1	Part No. 2	Part No. 3		DD604	12-Mar-11	16-Dec-10	2 months 24 days
4	19-Jul-11	DD591	DD611	DD233		DD778	12-Mar-11	02-Nov-10	4 months 10 days
5	12-Mar-11	DD778	DD392	DD604		DD185	11-Dec-10	11-Dec-10	0 months 0 days
6	07-Mar-11	DD624	DD373	DD209		DD192	20-Dec-10	20-Dec-10	0 months 0 days
7	16-Jan-11	DD373	DD208	DD778		DD208	16-Jan-11	16-Jan-11	0 months 0 days
8	28-Mar-11	DD781	DD626	DD373		DD209	07-Mar-11	07-Mar-11	0 months 0 days
9	06-Jan-11	DD936	DD581	DD268
10	04-Feb-11	DD778	DD388	DD847
11	10-Oct-10	DD354	DD906	DD272
12	16-Dec-10	DD604	DD746	DD244
13	22-Nov-10	DD959	DD778	DD515
14	03-Apr-11	DD948	DD562	DD973
15	09-Dec-10	DD183	DD122	DD700
16	02-Nov-10	DD778	DD953	DD653
17	14-Jan-11	DD107	DD581	DD402
18	28-Nov-10	DD634	DD288	DD915
19	20-Dec-10	DD192	DD603	DD778
20	03-Mar-11	DD438	DD604	DD508
21	11-Dec-10	DD185	DD819	DD920
Sheet3

Formulae in cells G2 and H2 are ARRAY-ENTERED using Ctrl + Shift + Enter, and copied down.
It doesn't find lowest/highst row numbers, it finds max and min dates, which I think is what you want.
Formula in cell I2 is normally-entered, in which I've used a non-documented excel function.

Dates don't need to be sorted.
I've highlighted part nos. which repeat.

I haven't understood what you want by Average Monthly Usage, but hopefully I've given you enough to get started?

 

[mwjessica]

Hi p45cal,

Thanks very much for your response. Here is a picture to hopefully clarify my intentions.

The part that I get stuck on is calculating the "Months of Order History" automatically. For instance: For part number A100, the months of order history should be 02/18/11 minus 09/07/10 divided by 30. So, can I pull the two dates based on the first and last occurrences of quantities (4 and 2) in column D?

Thank you for your help!

-Jessica

 

[p45cal]

Ah, an Excel Jeanie shot, or screenshot or picture always helps.

Excel Workbook
	A	B	C	D	E	F
1	Order date	Order type	order #	PN# A100	PN# A200	PN# A300
2	10-Oct-10	CUSTOMER	S103			11
3	07-Sep-10	CUSTOMER	S105	4
4	23-Oct-10	CUSTOMER	S106		10	10
5	04-Nov-10	CUSTOMER	S111
6	15-Dec-10	CUSTOMER	S114		12	12
7	01-Jan-11	CUSTOMER	S117	12
8	18-Feb-11	CUSTOMER	S119	2	6	4
9	31-Mar-11	CUSTOMER	S120		24
10			Total Qty	18	52	37
11			Earliest date	07-Sep-10	23-Oct-10	10-Oct-10
12			Most recent date	18-Feb-11	31-Mar-11	18-Feb-11
13			Months of Order History	5 months 11 days	5 months 8 days	4 months 8 days
14			Avg Monthly Qty	3.3	9.8	8.5
Sheet

Formule in D11 and D12 need to be ARRAY-ENTERED (individually) as described before, D13 and D14 should be entered normally, then you can copy to the right.
Again, the dates need not be sorted, max and min dates are taken.

D13 can be shortened to give just month numbers:
=DATEDIF(D$11,D$12,"m")
It rounds down.
Then D14 can be:
=D$10/D$17
but remember the calculation uses the rounded down no. of months.

 

[p45cal]

A small tweak to cope with missing dates:

Excel Workbook
	D
11	07-Sep-10
12	01-Jan-11
Sheet3

when the latest/earliest valid date will be taken instead.

If you use the suggestion of dividing by the rounded down no. of months for the average monthly quantity, occasionally this will lead to division by zero errors when the time between first and last orders is less than a month.

 

[mwjessica]

Hi p45cal,

This worked! This is what I did:

Earliest Date (D2): {=MIN(IF(ISNUMBER(D$7:D140),$A$7:$A140))}
Latest Date (D3): {=MAX(IF(ISNUMBER(D$7:D140),$A$7:$A140))}
Months of History (D4): =(D3-D2)/30
Monthly Average (D5): =IF(D4>0,ROUND(ShipmentHistory[[#Totals],[A100]]/D4,0),"")

Thank you so much! I don't have experience with arrays, so I couldn't have done this without you!

-Jessica