Formula for a cell number

[GetPaidOl9]

Hello Friends,

I have a huge spreadsheet with 18 columns and >2000 rows. In column 'N' there is data that change by changing the data in columns 'C', 'D', 'E' and 'G'. On the top of column 'N', I have put the the formula "=MIN(N10:N2087)". I get the ans correctly, but can I get the cell number (in the adjacent cell) which contains that minimum values. I don't want to scroll whole sheet every time when the cell number changes.

Thanks in advance.

 

[GetPaidOl9]

hmmm, what the formula does is it looks for the first instance of the MIN value in the range N10:N2087, then return the corresponding value from column A.. could it be that there are duplicates in column N? or maybe you have to change the column from N to R?

Opss.. silly me... I just copy-pasted the formula.. Done just by changing 'N' to 'R'.

Thank you very much, again.

 

[Aladin Akyurek]

Hello Friends,

I have a huge spreadsheet with 18 columns and >2000 rows. In column 'N' there is data that change by changing the data in columns 'C', 'D', 'E' and 'G'. On the top of column 'N', I have put the the formula "=MIN(N10:N2087)". I get the ans correctly, but can I get the cell number (in the adjacent cell) which contains that minimum values. I don't want to scroll whole sheet every time when the cell number changes.

Thanks in advance.

In A10 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF($N$10:$N$2087=MIN($N$10:$N$2087),ROW($N$10:$N$2087)),ROWS($A$10:A10)),"")

This gives the native row numbers on which the MIN value occurs.

 

[GetPaidOl9]

PS: May I ask another question here only or separate post?

 

[Aladin Akyurek]

You should (re-)consider post #12, not dismiss it.

PS: May I ask another question here only or separate post?

Yes, but in a separate thread please.

 

[proficient]

In A10 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF($N$10:$N$2087=MIN($N$10:$N$2087),ROW($N$10:$N$2087)),ROWS($A$10:A10)),"")

This gives the native row numbers on which the MIN value occurs.

Aladin could you please explain your formula cuz many times I saw the same formula you refer to solve the problem with lil changes!

 

[Aladin Akyurek]

Aladin could you please explain your formula cuz many times I saw the same formula you refer to solve the problem with lil changes!

I didn't get what you say. Would you re-word that?

 

[GetPaidOl9]

@ jarjarbingie:

Hello,

I told you "it is done by changing N to R" in post # 11.

But now I have another problem, the value (168.62) (image - http://i.imgur.com/li6ETey.jpg) when goes > +1, the result is 01-01-00 (though the minimum value refers to 08-08-15 only). Till 1.00 the result is correct as 08-08-15.

Please suggest correction. Thanks.

 

[proficient]

I didn't get what you say. Would you re-word that?

I want you to explain the SMALL and ROW functions... Many thanks in advance

 

[Aladin Akyurek]

I want you to explain the SMALL and ROW functions... Many thanks in advance

01. SMALL acts on numbers.

SMALL({2;3;4},2)

would for example yield 3.

While

SMALL({2;3;FALSE,5},ROWS($A$2:A2))

>>

SMALL({2;3;FALSE,5},1)

>> 2

and

SMALL({2;3;FALSE,5},ROWS($A$2:A4))

>>

SMALL({2;3;FALSE,5},3)

>> 5

02. ROW applied to a reference delivers row numbers (integers).

ROW(A2:A5)

>>

{2;3;4;5}

03. An expression like

IF(A2:A5="x",ROW(A2:A5))

yields row numbers corresponding to cells in A2:A5 which are equal to x. When only A2 = x and A5 = x, we get...

IF(A2:A5="x",ROW(A2:A5))

>>

{2;FALSE;FALSE;5}

SMALL applied on the foregoing above would yield:

2, then 5. These SMALL results can be fed to for example to an INDEX expression.

Hope this helps.

 

[proficient]

01. SMALL acts on numbers.

SMALL({2;3;4},2)

would for example yield 3.

While

SMALL({2;3;FALSE,5},ROWS($A$2:A2))

>>

SMALL({2;3;FALSE,5},1)

>> 2

and

SMALL({2;3;FALSE,5},ROWS($A$2:A4))

>>

SMALL({2;3;FALSE,5},3)

>> 5

02. ROW applied to a reference delivers row numbers (integers).

ROW(A2:A5)

>>

{2;3;4;5}

03. An expression like

IF(A2:A5="x",ROW(A2:A5))

yields row numbers corresponding to cells in A2:A5 which are equal to x. When only A2 = x and A5 = x, we get...

IF(A2:A5="x",ROW(A2:A5))

>>

{2;FALSE;FALSE;5}

SMALL applied on the foregoing above would yield:

2, then 5. These SMALL results can be fed to for example to an INDEX expression.

Hope this helps.

You are AWESOME!