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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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)
 

n_8dog

New Member
Joined
Oct 6, 2004
Messages
38
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)).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,906
Members
414,110
Latest member
docops

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
Top