Hello all,
What does this ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200))+1 equal to?
Thanks,
Row function
-
- Administrator
- Posts: 12605
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Row function
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
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
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Row function
Can you please let me know what the function is?
Regards,
Green Tea
-
- Administrator
- Posts: 12605
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Row function
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
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Row function
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
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.
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Row function
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.
Therefore we cannot really explain it by itself.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Row function
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,
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,
-
- Administrator
- Posts: 78483
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Row function
Once again, this is part of a larger formula. The use of SMALL has a role in that larger formula.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Row function
Hans and rest of the team,
Thanks for all inputs here.
Regards,
Thanks for all inputs here.
Regards,