Creating formula that uses the last number entry on right

M

Micky

I would like to create a formula the looks at a range of
12 cells horizontally. I want the formula to use the last
entry on right as the criteria for a if function. Example-
If the last entry on right in a range of 12 cells is
larger than all of the entries to the left of this cell,
say "yes", if not say "no".
 
F

Frank Kabel

Hi
try the following (if you have no blank cells between the left most
cell and the last entry cell):
1. to get the last filled cell:
=OFFSET(A1,0,COUNTA(A1:L1)-1)

2. For your comparison:
=IF(OFFSET(A1,0,COUNTA(A1:L1)-1)=MAX(A1:L1,"Yes","No")
 
B

Bob Phillips

Hi Micky,

Here is a version that caters for blank cells

=IF(INDEX(B1:IV1,1,MAX(IF(B1:IV1<>"",COLUMN(B1:IV1)))-COLUMN(B1:IV1)+1)=MAX(
B1:IV1),"Yes","No")

it is an array formula, so commit with Ctrl-Shift-Enter. The IV can be
replaced with your last applicable column.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top