I am trying to write a formula that captures whatever data is in the range furthest to the right. Hopefully the following example will clarify what I am trying to accomplish.
Range A1 through E1 contains the following numbers: | 4 | 2 | 6 | Blank | Blank |
I am trying to write a formula that appears in cell H1 that will pick up the number 6 as this is the number furthest to the right in the given range. The following formula will do this if there are no gaps in the middle of the data: =OFFSET(A1,0,COUNTA(B1:E1))
If the data in Range A1 through E1 contains the following: | 4 | 2 | Blank | 5 | 3 |
The =OFFSET(A1,0,COUNTA(B1:E1)) formula will display 5 rather than the 3.
Any thoughts on how to produce a formula that would always pick up the last non-blank data in the cell furthest to the right of a given range?
Range A1 through E1 contains the following numbers: | 4 | 2 | 6 | Blank | Blank |
I am trying to write a formula that appears in cell H1 that will pick up the number 6 as this is the number furthest to the right in the given range. The following formula will do this if there are no gaps in the middle of the data: =OFFSET(A1,0,COUNTA(B1:E1))
If the data in Range A1 through E1 contains the following: | 4 | 2 | Blank | 5 | 3 |
The =OFFSET(A1,0,COUNTA(B1:E1)) formula will display 5 rather than the 3.
Any thoughts on how to produce a formula that would always pick up the last non-blank data in the cell furthest to the right of a given range?