LARGE function?

[Sparky]

I have in cells A1,A8,A15,A22 etc the time a document was printed.
In cells A2,A9,A16,A23 etc I have a formula counting how much time has elapsed since the document was printed.

I have created a named range from A1 up to A99 for the cells with the elapsed time.

When I use the LARGE function to display the highest elapsed time value it displays the current time. What I require is for the formula to calculate the values if the time the document was printed is entered. For example if cells A1,A8 and A15 have the time a document was printed entered and A22 is still blank only take into account the time elapsed in cells A2,A9 and A16.


Thanks

 

[Aladin Akyurek]

Could you post a small sample along with the expected outcome?

 

[Sparky]

Based on the system clock at 19:30

A1 Time printed 18:00
A2 Time elapsed 01:30

A8 Time printed 18:20
A9 Time elapsed 01:10

A15 Time printed
A16 Time elapsed 19:30

The LARGE function to look at my named range which includes A2,A9 and A16 and to display 01:30 as the highest value as my answer and to completely overlook A16 due to the fact that there is no time entered in cell A15. In other words A16 is not calculating elapsed time.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MAX(
   IF(IF(MOD(ROW($A$1:$A$15)-ROW($A$1),7)=0,
       IF(ISNUMBER($A$1:$A$15),$A$1:$A$15)),$A$2:$A$16))

Does this yield the expected outcome?

Based on the system clock at 19:30

A1 Time printed 18:00
A2 Time elapsed 01:30

A8 Time printed 18:20
A9 Time elapsed 01:10

A15 Time printed
A16 Time elapsed 19:30

The LARGE function to look at my named range which includes A2,A9 and A16 and to display 01:30 as the highest value as my answer and to completely overlook A16 due to the fact that there is no time entered in cell A15. In other words A16 is not calculating elapsed time.

 

[Sparky]

Thanks for your reply Aladin. i received the NA error.

I have now changed the spreadsheet. A quick overview is that it is used to track sequenced car parts. Product description, sequence numbers (from and to), Q (quantity of sheets printed), printed by (person's initials), time printed and time elapsed since the print out was issued. When the sheet is returned to the office to confirm the parts have been sequenced the letter C (confirmed) is entered into the time elapsed cell thus overwriting the formula in the cell.

If you look at columns A and B I have typed what I would like to appear auomatically with the highest time next to the parts.

The system clock at the time of creating this HTML was 15:35. When I have attempted to use the LARGE function the system time always appears in the outcome.

Hope this clearer

Excel Workbook
	A	B	C	D	E	F	H	I	J	K	L	M	O	P	Q
1	*	*	15:35	*	*	*	*	*	Build Ratio		6	*	*	*	*
2	*	*	Sunday	*	*	*	*	*	Estate Ratio		16	*	*	*	*
3	*	*	29/06/2008	1							2
4	*	*	*
5	*	*	Description	From	To	Q	Printed by	Time Printed	Time Elapsed	From	To	Q	Printed by	Time Printed	Time Elapsed
6	Latches Left	09:08	00 Headlamps	6010	6124	1	AC	10:06	C	6130	6244	1	MM	14:00	01:35
7	Latches Right	09:07	00 Handbrakes	6020	6134	1	AC	11:45	C	6140	6254	1	MM	14:03	01:32
8	00 Cappings	09:04	00 Springs	8744	8828	1	AC	07:55	07:40	*	*	0	*	*	15:35
9	00 Door Handles	08:53	00 Door Handles	8756	8786	1	MM	06:42	08:53	*	*	0	*	*	15:35
10	LL Carpets	08:51	00 Grilles	8768	8906	1	AC	12:04	03:31	*	*	0	*	*	15:35
11	Quarter Lights	08:42	00 Cats	8768	8882	1	AC	12:00	03:35	*	*	0	*	*	15:35
12	Windshields RH6L	07:42	00 Cargo Blinds	8780	8972	1	AC	11:30	04:05	*	*	0	*	*	15:35
13	00 Springs	07:40	00 Waist Mouldings	8756	8924	1	AC	11:25	04:10	*	*	0	*	*	15:35
14	Disc & Calipers	07:16	00 Cappings	8720	8774	1	AC	06:31	09:04	*	*	0	*	*	15:35
15	Front Door Glass LH	05:42	LL Headlamps	8633	8824	10	AC	07:57	C	8825	8975	8	MM	14:02	01:33
16	Front Door Glass RH	04:42	LL Carpets	8662	8795	14	MM	06:44	08:51	*	*	0	*	*	15:35
17	00 Waist Mouldings	04:10	Disc & Calipers	8676	8819	6	AC	08:19	07:16	*	*	0	*	*	15:35
18	00 Cargo Blinds	04:05	Latches Left	8668	8703	1	AC	06:27	09:08	*	*	0	*	*	15:35
19	00 Cats	03:35	Latches Right	8678	8713	1	AC	06:28	09:07	*	*	0	*	*	15:35
20	00 Grilles	03:31	Quarter Lights	8682	8789	3	AC	06:53	08:42	*	*	0	*	*	15:35
21	00 Headlamps	01:35	Windshields RH6L	8768	8902	9	AC	07:53	07:42	*	*	0	*	*	15:35
22	LL Headlamps	01:33	Backlights LH6L	8768	8902	9	AC	08:53	C	*	*	0	*	*	15:35
23	00 Handbrakes	01:32	Front Door Glass LH	8682	8781	5	AC	09:53	05:42	*	*	0	*	*	15:35
24	*	*	Front Door Glass RH	8682	8781	5	AC	10:53	04:42	*	*	0	*	*	15:35
25	*	*	Rear Door Glass LH	8810	8899	5	AC	14:26	C	*	*	0	*	*	15:35
26	*	*	Rear Door Glass RH	8810	8899	5	AC	14:29	C	*	*	0	*	*	15:35
office

 

[Sparky]

Could anyone possibly answer this problem.

Thanks in advance

 

[Sparky]

Is there any hope for this poser or do you think it requires wording better?

Thanks

 

[DonkeyOte]

Can you not change your formulae in Q to only calculate time elapsed if from/to are specified in preceeding columns ?

eg Q6 instead of =$C$1-$P6 have =IF($K6=0,0,$C$1-$P6)

then it's relatively straightforward to work out the max elapsed time by simply doing

Max elapse for "00 Headlights" is then =MAX($J6,$Q6)

This is assuming each part appears in only one row in Col C

If this isn't resolving your issue please elaborate.

 

[Sparky]

lasw10

Thanks for the reply it does the job. I have a similar formula hidden in column G for when the number in column D is higher than the number in column E (after 9999 we start again at 0001). I can't believe I overlooked something relatively simple. Thanks for giving me a proverbial kick up the a***.

One last thing how would I display the top 3 highest times from the elapsed columns along with their part name?



Once again thanks