In this article, we will learn how to make a difference with the last non-blank cell in Excel.
Let's first get some knowledge about the function first.
The LOOKUP function finds the estimated value in the range and returns the value from the range or its corresponding value.
Here, we will be using one of the properties 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. We will be using the syntax below.
|= 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 it by an example.
Here we have some values measured and recorded results of it in a column. There are some blank cells among the data.
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.