FIRST MAXIMUM value in a range

[hsandeep]

In column D: from D1 to D12 there are 12 values (numerical only).
In column E: from E1 to E12 there are 12 values (numerical only) & each rows of column E corresponds to the same row# of column D i.e. E1 corresponds to D1, E2 to D2 & so on till E12 corresponds to D12.
In column D (from D1 to D12) I want to find the FIRST MAXIMUM value available starting from row#1 i.e. D1 & going down by 1 row till D12. After this FIRST MAXIMUM value is found then the value in the corresponding row# of column E needs to be shown in a separate cell G5.
What formula should be punched in G5?
Plus if either of ‘#DIV/0!’, ‘#N/A’ & ‘#VALUE!’ appears in any row of column D, then it should ignore this & check next down row for the FIRST MAXIMUM value (if the first maximum value is not found above the 1<SUP>st</SUP> appearance of #DIV/0!).
Example: D1=2, D2=3.4, D3=#DIV/0!, D4=4, D5=2.2, D6=7, D8=#DIV/0!, D9=3, D10=4, D11=4.6 & D12=1. Result: is 7 which appears in D6 & so E6 should be shown in G5.
Example: D1=2, D2=9.4, D3=#DIV/0!, D4=4, D5=2.2, D6=7, D8=#DIV/0!, D9=3, D10=4, D11=4.6 & D12=1. Result: is 9.4 which appears in D2 & so E2 should be shown in G5.
Thanks in advance.
hsandeep

 

[Domenic]

Unclear... If you have the following data...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>800</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>3.4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>#DIV/0!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>750</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>2.2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>300</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>#DIV/0!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>H</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>650</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>4.6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>K</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>325</TD></TR></TBODY></TABLE>

...what would you expect as your result? Can you post the exact values that you'd like to see returned to your worksheet?

 

[hsandeep]

Domenic's post has given me an idea to simplify:
=INDEX(E1:E12,MATCH(MAX(IF(ISNUMBER(D1:D12),D1:D12)),D1:D12,0))
which didn't need array-entering, normal entering sufficed.

P45Cal,
When entered as an array formula with [Ctrl] + [Shift] + [Enter] kept pressed down, the outcome was shown as ‘F’. But the ‘final outcome should be a blank in this case since ‘F’ is a non-numerical value.
Under normal entering, it yielded #DIV/0!.
Comments please.
hsandeep

 

[hsandeep]

What version of excel are you using?

=IF(ISNUMBER(INDEX(E1:E12,MATCH(MAX(IF(ISNUMBER(D1:D12),D1:D12)),D1:D12,0))),INDEX(E1:E12,MATCH(MAX(IF(ISNUMBER(D1:D12),D1:D12)),D1:D12,0)),"")
normally entered.

P45CAL,
The formula was entered as an array formula & then the outcome was a ‘blank’. It is OK for this set of values. But DOES THIS FORMULA (in G5) takes care of all criteria such that the ‘eligible’ values passed from the E column SHOULD NOT be a value which is either F alphabet/(s) or symbols or mix of alphabet/(s) & symbols or mix of alphabet/(s) & numerical or any value other than numerical?
In all such cases, G5 should display a blank (null i.e. “”) only.
Please revert.
hsandeep

 

[hsandeep]

Domenic,
3 (Three) rows in column D passes the 1<SUP>st</SUP> test (FIRST MAXIMUM NUMERICAL). D6, D10 & D12. Out of 3, the FIRST NEAREST row is row #6 (since 6<10 and 6<12 also). So D6 ‘clears’ the 1<SUP>st</SUP> test successfully & accordingly ONLY E6 is eligible & has to be tested for clearing the 2<SUP>nd</SUP> test.
Here E6=alphabet (F), so disqualified & G5=”” (blank or null). Final result here.
For ‘clearing’ the 2<SUP>nd</SUP> test, the ‘eligible’ cell address from the E column SHOULD NOT contain a value which is either F alphabet/(s) or symbols or mix of alphabet/(s) & symbols or mix of alphabet/(s) & numerical or any value other than numerical. In all such cases, G5 should display blank (null i.e. “”) only. G5 should display ONLY NUMERICAL values from the E column else exhibit a blank.
Please change the formula of the cell G5 to accommodate above criteria also.
hsandeep

 

[Domenic]

Domenic,
3 (Three) rows in column D passes the 1<SUP>st</SUP> test (FIRST MAXIMUM NUMERICAL). D6, D10 & D12. Out of 3, the FIRST NEAREST row is row #6 (since 6<10 and 6<12 also). So D6 ‘clears’ the 1<SUP>st</SUP> test successfully & accordingly ONLY E6 is eligible & has to be tested for clearing the 2<SUP>nd</SUP> test.
Here E6=alphabet (F), so disqualified & G5=”” (blank or null). Final result here.
For ‘clearing’ the 2<SUP>nd</SUP> test, the ‘eligible’ cell address from the E column SHOULD NOT contain a value which is either F alphabet/(s) or symbols or mix of alphabet/(s) & symbols or mix of alphabet/(s) & numerical or any value other than numerical. In all such cases, G5 should display blank (null i.e. “”) only. G5 should display ONLY NUMERICAL values from the E column else exhibit a blank.
Please change the formula of the cell G5 to accommodate above criteria also.
hsandeep

In that case, the formula offered by p45Cal in Post #8 should return the desired result. And, yes, from the looks of it, only if the corresponding value in Column E is a number will that value be returned. Otherwise, the formulla returns an empty/null string.

 

[p45cal]

P45Cal,
When entered as an array formula with [Ctrl] + [Shift] + [Enter] kept pressed down, the outcome was shown as ‘F’. But the ‘final outcome should be a blank in this case since ‘F’ is a non-numerical value.
Under normal entering, it yielded #DIV/0!.
Comments please.
hsandeep

Experimenting on a macine with excel 2003 it seems the above formula does need to be array-entered (I had been using excel 2007).
The reason it returns F is because this formula was devised and my message submitted only minutes before you introduced scope-creep in message#6.

see next message.

 

[p45cal]

P45CAL,
The formula was entered as an array formula & then the outcome was a ‘blank’. It is OK for this set of values. But DOES THIS FORMULA (in G5) takes care of all criteria such that the ‘eligible’ values passed from the E column SHOULD NOT be a value which is either F alphabet/(s) or symbols or mix of alphabet/(s) & symbols or mix of alphabet/(s) & numerical or any value other than numerical?
In all such cases, G5 should display a blank (null i.e. “”) only.
Please revert.
hsandeep

[referring to the formula in msgs #8 & #13]
I'm pretty sure it does.
Surely this is not difficult for you to test?

ps. Even in excel 2003, this longer formula didn't seem to require array-entering, but it might be safer to array-enter it (do a few trials youself?).

 

[hsandeep]

P45CAL,
I have proceeded ahead & started using the formula: =IF(ISNUMBER(INDEX(E1:E12,MATCH(MAX(IF(ISNUMBER(D1:D12),D1:D12)),D1:D12,0))),INDEX(E1:E12,MATCH(MAX(IF(ISNUMBER(D1:D12), D1:D12)),D1:D12,0)),"").
The formula has been entered as an array formula.
I am going to keep track of how much ‘load’ the above formula (in G5) is able to take care of while fulfilling the required criteria.
The required criteria that needs to be taken care of by this formula is: the value passed through the E column SHOULD NOT be a value which is either F alphabet/(s) or symbols or mix of alphabet/(s) & symbols or mix of alphabet/(s) & numerical or any value other than numerical & for all such values the formula should treat these values as ‘ineligible’ & display a blank (null i.e. “”). The formula SHOULD ONLY ALLOW numerical value & display the value as a Result in the G5 cell.
Sir, in future, if you feel a change in the above formula would improvise its result, please DO LET ME KNOW as and when possible/available.
Comments please.
hsandeep

 

[p45cal]

Array-entered formulae can be quite an overhead on calculation if the ranges they refer to are large-ish. D1:D12 is probably smaller than your actual data. How big are your ranges?
To test whether you can get away with using the formula normally entered, put the same formula in the adjacent column and normally-enter it there. Now compare values in the two columns. Then play about, trying to make the formulae go wrong by adjusting the data in clumns D and E to test both results. If after exhaustive testing you always get the same results in both columns, you should be safe to abandon the array-entered version.

On another point, I'm 99.7% sure that the isnumber functoin will handle things as you want, but be aware, that if numbers in column E are 'stored as text' (say because the cell has been formatted as Text, or the number has been preceded with an apostrophe), the Isnumber function treats this number-stored-as-text as text, so the formula will return a blank.

Regarding letting you know if I think of a way of improving the formula, of course I will, if I can find the thread again!