modification required

[mole999]

Any idea how to modify this to ignore blank cells. with the exact range encompassed it works to return the highest alphanumeric value

={INDEX(A2:A7,MATCH(MAX(COUNTIF(A2:A7,"<"&A2:A7)),COUNTIF(A2:A7,"<"&A2:A7),0))}

I want to use the value to allow SQL to import greater than the max value, from a column that is often sorted, and I can't predict where the max value might be sitting at the time of updated, currently over 24,000 rows and growing.

I could code it to return at that moment in time, but would prefer persistence of vision

 

[StephenCrump]

Re: modifiaction required

I might be missing something here, but I don't immediately see how your formula would be be impacted by blank cells?

 

[mole999]

Re: modifiaction required

i don't know either but in a small test blank seems to fail it

Sheet1

	A
e01
a01
d01
e01
b01
c02

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 72px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A1 {=INDEX(A2:A7,MATCH(MAX(COUNTIF(A2:A7,"<"&A2:A7)),COUNTIF(A2:A7,"<"&A2:A7),0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



testing again, and it works with blanks so i'm not sure whats happening

TOPTIP, check for calculation and length of selection