# LARGE function?

#### Sparky

##### Board Regular
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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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

#### Sparky

##### Board Regular
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.

##### MrExcel MVP
Control+shift+enter, not just enter:
Code:
``````=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

##### Board Regular

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
ABCDEFHIJKLMOPQ
1**15:35*****Build Ratio6****
2**Sunday*****Estate Ratio16****
3**29/06/200812
4***
5**DescriptionFromToQPrinted byTime PrintedTime ElapsedFromToQPrinted byTime PrintedTime Elapsed
7Latches Right09:0700 Handbrakes602061341AC11:45C614062541MM14:0301:32
800 Cappings09:0400 Springs874488281AC07:5507:40**0**15:35
900 Door Handles08:5300 Door Handles875687861MM06:4208:53**0**15:35
10LL Carpets08:5100 Grilles876889061AC12:0403:31**0**15:35
11Quarter Lights08:4200 Cats876888821AC12:0003:35**0**15:35
12Windshields RH6L07:4200 Cargo Blinds878089721AC11:3004:05**0**15:35
1300 Springs07:4000 Waist Mouldings875689241AC11:2504:10**0**15:35
14Disc & Calipers07:1600 Cappings872087741AC06:3109:04**0**15:35
15Front Door Glass LH05:42LL Headlamps8633882410AC07:57C882589758MM14:0201:33
16Front Door Glass RH04:42LL Carpets8662879514MM06:4408:51**0**15:35
1700 Waist Mouldings04:10Disc & Calipers867688196AC08:1907:16**0**15:35
1800 Cargo Blinds04:05Latches Left866887031AC06:2709:08**0**15:35
1900 Cats03:35Latches Right867887131AC06:2809:07**0**15:35
2000 Grilles03:31Quarter Lights868287893AC06:5308:42**0**15:35
2300 Handbrakes01:32Front Door Glass LH868287815AC09:5305:42**0**15:35
24**Front Door Glass RH868287815AC10:5304:42**0**15:35
25**Rear Door Glass LH881088995AC14:26C**0**15:35
26**Rear Door Glass RH881088995AC14:29C**0**15:35
office

#### Sparky

##### Board Regular
Could anyone possibly answer this problem.

#### Sparky

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

Thanks

#### DonkeyOte

##### MrExcel MVP
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

##### Board Regular
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

Replies
4
Views
130
Replies
3
Views
193
Replies
2
Views
60
Replies
8
Views
196
Replies
1
Views
184

1,191,273
Messages
5,985,691
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back