Row function

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Row function

Post by BittenApple »

Hello all,
What does this ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200))+1 equal to?
Thanks,

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Row function

Post by StuartR »

On my version of Excel that is a spill formula which gives the values 1 to 199
If I enter it into cell A1, then A1 gets the value 1, A2 gets 2, all the way down to A199 which gets 199
StuartR


BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Row function

Post by BittenApple »

:thankyou: :thankyou:
Can you please let me know what the function is?

Regards,
Green Tea

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Row function

Post by StuartR »

I have no idea, it just seems to be defining an array of constants. The result remains the same regardless of what data is on the worksheet.
StuartR


adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Row function

Post by adeel1 »

Alone this line is doing nothing except producing array from 1 to 199 rows num

This should be part of some formula like if or aggregate or any other where this line will return that specific row num where condition will met and blank/error for false condition(depends on formula)

This could be more shorter something like this =+ROW(Sheet2!$A$2:$A$200)-1

This is the bit smart way to write like this ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200))+1

please review attached, if you have 365 then you can see array return otherwise press F9

Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Row function

Post by HansV »

BittenApple, that is just a part of a larger formula. It is rather meaningless by itself, but it has a function in the larger formula.
Therefore we cannot really explain it by itself.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Row function

Post by BittenApple »

Thanks for all,
I think Adeel well explained it.

Then in this function:
SMALL(IF((Sheet2!$A$2:$A$200=$A$2)*(Sheet2!$B$2:$B$200=$B$2),ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200)+1),ROWS($A$5:$A22)),)

The function below gives the answer:
ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200)+1)

Why do we need to enter Small....ROWS($A$5:$A22)?

Or we can enter ROWS($A$5:$A22) only? As we pull the function down, the rows would change.

Regards,

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Row function

Post by HansV »

Once again, this is part of a larger formula. The use of SMALL has a role in that larger formula.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Row function

Post by BittenApple »

Hans and rest of the team,
Thanks for all inputs here.
Regards,
:thankyou: :thankyou: :thankyou: :thankyou: :thankyou: :thankyou: