Formula to capture data in the cell furthest to the right

Hanz77

Board Regular
Joined
Sep 21, 2006
Messages
87
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?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi

If just numerical data you could use:
Excel Workbook
ABCDEF
15311
28522
3477
42d2
Sheet1
Excel 2003
Cell Formulas
RangeFormula
F1=LOOKUP(99^99,A1:E1)
 
Upvote 0
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?

Just browsing excel questions and saw your post. One issue I see is that your counta formula starts in B1, not A1. Try =OFFSET(A1,0,COUNTA(A1:E1)).
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top