|
|
|||
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.
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.
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 1÷24 (=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.
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 |
 |
Click here for details about obtaining this file |
| file:xlvariousone.htm | © meadinkent.co.uk 2011 | Page last updated Nov11 |
