In this article, we will learn how to Difference with the last non-blank cell in Excel.
Let's first get some knowledge about the function first.
LOOKUP function finds the estimated value in the range and returns the value from the range or its corresponding value.
|=LOOKUP (lookup_value, lookup_vector, [result_vector])|
lookup_value: value to look up for
lookup_vector: array where the function looks for the lookup_value.
But here we will be using its one of the attributes of the LOOKUP function i.e. if LOOKUP function can't find an exact match, it returns the last & nearest value. It returns the last found value. So we will be using the below syntax.
|= IF ( Cell <>"" , Cell - LOOKUP ( 2 , 1 / ( exp_range<>"" ), exp_range ) , last_result )|
Here we will be using one more attribute of excel which is exp_ranges. Expanding ranges is a mixed reference where absolute reference ($A$1) and relative reference (A1) both are used.
Let’s understand this by using it in Example
Here we have some values measured and recorded results of it in a column. There are some of the blank cells which are recorded when the measurement must have been recorded.
Use the formula in the D4 cell:
|= IF ( C3<>"" , C3 - LOOKUP ( 2 , 1 / ( $C$2:C2<>"" ), $C$2:C2), D2)|
Here the range is given as expanding range and cell reference for the single cell reference.
As you can see in the above snapshot the difference of 151 with 150 comes out to be 1. That seems fine. Now we will copy the formula to the rest of the cells using the Ctrl + D Shortcut or drag down cells option of excel.
As you can see the formula in the last cell which is = IF ( C14<>"" , C14 - LOOKUP(2,1/($C$2:C13<>"") , $C$2:C13) , D13 ) and you can see I added 0 at the start of the table to complete the table. Below are some of the observations about this formula.
Hope you understood how to take Difference form the last non-blank cell in Excel. Explore more articles on LOOKUP functions here. Please feel free to state your queries below in the comment box. We will certainly help you.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.