Create Excel Dashboard using OFFSET, SUM and MATCH functions

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.
  • 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.
IN OUR EXAMPLE, we're using MATCH($B$3,$B$5:$M$5,0), which as per the definition of MATCH function returns the index number / position of cell B3 in the array B5:M5. So, if you'll choose APR, it will return 1, in case of AUG, it will return 5 and so on....Please note, we choose 0 for the exact match as well.


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)
In our Sample dashboard, we are using SUM(OFFSET(B6,0,0,1,MATCH($B$3,$B$5:$M$5,0))), so it will return the cumulative sum of the sales of Wine up to the selected month in cell B3.


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 :)












Comments

Anonymous said…
Wow, it's good but I need more elaboration on Conditional formatting. Thanks.
andrei said…
is any possibility to achieve something like that: if in April, the product "x" shows there, if not, do not look product "x"