As we learned in our previous article on OFFSET function, when we increase either the row height or column width in the offset function "=OFFSET(A1,2,0,1,1)" to more than 1, the reference is converted to a range from a single cell value. Now this range can be provided to various formulas where it will be used as Dynamic Range.
Now we'll try to understand how to use OFFSET function in conjunction with SUM Function. Before we begin, let's have a look at a dashboard where we're effectively using SUM Function in conjunction with OFFSET and MATCH function.
Unwinding the Nested Formula and Conditional Formatting used in the Sample Dashboard
FORMULA USED:
=SUM(OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0)))
1. MATCH Function
Syntax: MATCH(lookup_value,lookup_array,match_type)
Definition: It returns the relative position of an item in an array that matches a specified value in a specified order. It is used instead of the LOOKUP functions when we need the position of an item in a range instead of the item itself. (Source: Excel Help on Match Function)
Lookup_value is the value you use to find the value you want in a table.
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
2. OFFSET Function
Here we used Type-IV OFFSET Function which takes single cell as input but returns a range of single/multiple cells depending on the return value of MATCH function.
In our example, we're using OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0)). So, if we choose B3 as APR, it will be OFFSET(B6,0,0,1,1) which means B6, similarly if we choose B3 as OCT, it will be OFFSET(B6,0,0,1,7) which means B6:H6.
Read the full article on OFFSET Function here.
3. SUM Function
Definition: Adds all the numbers in a range of cells.
Syntax: SUM(number1,number2, ...)
Number1, number2, ... are 1 to 255 arguments for which you want the total value or sum.
Remarks
4. Conditional Formatting
Formula Used: =IF(B$5<=$B$3,1,0)
Note: The game of this conditional formatting lies in what is absolute reference and what is relative reference.
Applied on: =$B$6:$M$11
Click here to DOWNLOAD THE DASHBOARD. Happy Excel Dashboarding :)
Now we'll try to understand how to use OFFSET function in conjunction with SUM Function. Before we begin, let's have a look at a dashboard where we're effectively using SUM Function in conjunction with OFFSET and MATCH function.
Unwinding the Nested Formula and Conditional Formatting used in the Sample Dashboard
FORMULA USED:
=SUM(OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0)))
1. MATCH Function
Syntax: MATCH(lookup_value,lookup_array,match_type)
Definition: It returns the relative position of an item in an array that matches a specified value in a specified order. It is used instead of the LOOKUP functions when we need the position of an item in a range instead of the item itself. (Source: Excel Help on Match Function)
Lookup_value is the value you use to find the value you want in a table.
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
- If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
- If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
- If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
- If match_type is omitted, it is assumed to be 1.
2. OFFSET Function
Here we used Type-IV OFFSET Function which takes single cell as input but returns a range of single/multiple cells depending on the return value of MATCH function.
In our example, we're using OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0)). So, if we choose B3 as APR, it will be OFFSET(B6,0,0,1,1) which means B6, similarly if we choose B3 as OCT, it will be OFFSET(B6,0,0,1,7) which means B6:H6.
Read the full article on OFFSET Function here.
3. SUM Function
Definition: Adds all the numbers in a range of cells.
Syntax: SUM(number1,number2, ...)
Number1, number2, ... are 1 to 255 arguments for which you want the total value or sum.
Remarks
- Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. So, "5" will become 5, TRUE will become 1 and False will become 0.
- If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
- Arguments that are error values or text that cannot be translated into numbers cause errors. If there is any #NA, #VALUE etc. error is present in the reference range, it will return the same error ignoring every other thing present in the reference range. (Source: MS Excel Help on SUM Function)
4. Conditional Formatting
Formula Used: =IF(B$5<=$B$3,1,0)
Note: The game of this conditional formatting lies in what is absolute reference and what is relative reference.
Applied on: =$B$6:$M$11
Click here to DOWNLOAD THE DASHBOARD. Happy Excel Dashboarding :)
BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010
Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions
or looking for a job ?
Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs
Comments