| Feedback | Complete Excel Index |

More Excel spreadsheet functions

Large and Small

The MIN() and MAX() functions will return the lowest and highest values from a range of numbers. Less well known are the SMALL() and LARGE() functions which return the nth smallest or largest value in a range of numbers. For example =LARGE(F3:F8,3) will return the third highest value (462) from the range F3:F8. This is used in the following example to remove the extreme values.

- E F G H I J K L
2   Meals     Min Max Mid Avg
3 Ward A 450   All Wards 195 530 363 425
4 Ward B 530   Mid Wards 432 480 456 456
5 Ward C 480            
6 Ward D 432   [I3] =MIN(F3:F8)   [J3] =MAX(F3:F8)
7 Ward E 462   [I4] =SMALL(F3:F8, 2)   [J4] =LARGE(F3:F8, 2)
8 Ward F 195   [L4] =SUMPRODUCT((F3:F8)*(F3:F8>=I4)*(F3:F8<=J4))/4

The 'Mid Wards' line (H4:L4) excludes the smallest (Ward F) and largest (Ward B) values. These were considered to be untypical and distorting the analysis of the data. The Mid and Average values of the adjusted range, better reflect the main grouping of values.

Would you like to learn more?

Excel 2010 Bible by John Walkenbach (with CD)

Cell L3 contains a normal (mean) average which is below all of the values in the middle group and has been unduly influenced by the exceptional low value (195). The adjusted average (in L4) uses the SUMPRODUCT() function and applies the Large and Small results as criteria to limit the calculation to the more typical and representative middle values.

Subtracting times which pass over midnight

If two cells contain time values, one can simply be subtracted from the other to determine the length of time elapsed. This is often required to calculate the length of a shift on a timesheet spreadsheet.

If the start time is before midnight and the finish time is the next day, Excel will appear to get the answer wrong because it does not understand that a day has passed.

- B C D E
12 Timesheet data    
13 Staff Start Finish Time Wkd
14 Pyke 08:00 17:15 9:15
15 Fraser 12:00 19:30 7:30
16 Wilson 17:45 06:00 12:15
17 Jones 20:00 06:30 10:30
18    www.meadinkent.co.uk

The following solution to avoid this problem works where shifts are always less than 24 hours in duration. Excel saves each day as a value of 1 and each hour is 124 (=0.04167). 6:00am tomorrow morning can therefore equate to 1.25

The formula in [E14] =IF(D14>C14, D14-C14, 1+D14-C14) adds 1 if the finish time is earlier than the start time.

Displaying the directory path and filename on a worksheet

The Excel CELL() function returns many different bits of information about your current spreadsheet project. One helpful purpose is to show the complete drive and folder name (where the file is stored on your computer), the spreadsheet name and the worksheet name.

Function format Details returned
=CELL("filename", A1) D:\MyDocs\Excel\[WebExcelFunctions.xls]Various

This is an alternative to saving the worksheet or filename in the page footer.

Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about
obtaining this file

file:xlvariousone.htm meadinkent.co.uk 2011 Page last updated Nov11