Useful Worksheet Functions

1. Counting the number of values that are between two numbers (between say 3.5 and 4.5):
   
    {=SUM(IF((ARange > 3.5)*(ARange < 4.5),1,0))}
   
   where ARange is a defined range (such as A1:A4500) and braces indicate that the formula is entered as an array (i.e. by using ctrl + shift + enter).
   
   
2. Summing cells on consecutive worksheets:
   
    =SUM(Sheet1:Sheet5!ARange)
   
   where ARange is a range of cells such as B20:D30 and Sheet1:Sheet5 includes the worksheets Sheet1 and Sheet5 and all worksheets in between.
   
   Sheet1:Sheet5!ARange is called a 3-d reference. For more information about 3-D references and how they are used see Excel's help file.
   
   
3. Finding a value in one column and returning the value in the cell next to it.
   
   As an example, suppose you want to look for a value in the range A100:A250 and return the neighboring value in the range B100:B250. To achieve this you can use:
   
    =OFFSET(B100,MATCH(A1,A100:A250,0)-1,0)
   
   where cell A1 contains the value that you want to look for.
   
   If there is no match for A1 in A100:A250 then the function returns an error.
   If there are several instances of A1 in A100:A250 then the first such instance will be used i.e. the first occurrence of A1 as we check from cell A100 to cell A250 in ascending row order.
   
   
4. Determining the date of the last day of a given month in a given year:
    =DATE(A1,A2+1,1)-1
   
   where cell A1 contains the year and cell A2 contains the month.
   
   
5. Calculating the average of a range excluding the minimum value in that range:
   
    =(AVERAGE(ARange)*COUNT(ARange)-MIN(ARange))/(COUNT(ARange) - 1)
   
   where ARange is a range of cells such as A1:A100.
   
   Note that if the minimum value is not unique then this formula only excludes the first occurrence of the minimum value. If you want to exclude all occurrences of the minimum value then you can use:
   
    {=SUM(IF(ARange=MIN(ARange),0,ARange))/SUM(IF(ARange=MIN(ARange),0,1))}
   
   where the braces indicate that the formula is entered as an array (i.e. by using ctrl + shift + enter).
   
   
6. Checking whether or not a given year is a leap year:
   
    =DAY(DATE(A1,3,1)-1)=29
   
   where cell A1 contains the year.
   
   

Email this article to a friend

Write to the editor

UltraSleuth Compare, check, analyze and document your Excel projects with UltraSleuth. Gain confidence in your spreadsheet results Speed up your spreadsheet development Understand third party spreadsheets Create an audit trail  Learn More...