Counting how much number is in ascending order in a row.

[motilulla]

Using Excel 2010
Hello,

I need a Formula or VBA which can count numbers are ascending order in a row. From the 1st starting number, for example…..

Row1 (4-7-8-10) =4, row2 (4-7-18) =3, row3 (4-7) =2, row5 (4-7-8-10-39) =5

n1	n2	n3	n4	n5	Ans
4	7	8	10	9	4
4	7	18	10	9	3
4	7	12	10	29	2
4	7	8	10	39	5

Regards,
Moti

 

[Special-K99]

Not saying I'll answer this but
Will there only ever be 5 columns to check?
Also what happens if one of your rows reads

1 - 2 - 1 - 3 - 4

Is the result 2 (1 -2)
or 3 (1 - 3 -4) ?

 

[kvsrinivasamurthy]

If row 2 is
4 - 7 - 18 - 10 - 9 - 13
How you count
4-7-18 count is 3
or
4-7-13 + 9-13 Total count 5

 

[motilulla]

Not saying I'll answer this but
Will there only ever be 5 columns to check?
Also what happens if one of your rows reads

1 - 2 - 1 - 3 - 4

Is the result 2 (1 -2)
or 3 (1 - 3 -4) ?

Hello Special-K99, in this case....the result 2 (1 -2)

 

[motilulla]

If row 2 is
4 - 7 - 18 - 10 - 9 - 13
How you count
4-7-18 count is 3
or
4-7-13 + 9-13 Total count 5

hello kvsrinivasamurthy,
If row 2 is
4 - 7 - 18 - 10 - 9 - 13
How you count
4-7-18 count is 3....this is correct

 

[Peter_SSs]

Hello Special-K99, in this case....the result 2 (1 -2)

It sounds then that the increasing values must start in thee first column. If that is so, try
(Looks to me like your third example result is incorrect since 4-7-12 is increasing)

24 02 26.xlsm
	A	B	C	D	E	F
1	n1	n2	n3	n4	n5	Ans
2	4	7	8	10	9	4
3	4	7	18	10	9	3
4	4	7	12	10	29	3
5	4	7	8	10	39	5
6	15	12	10	8	7	1
7	4	7	6	8	9	2
Ascending

Cell Formulas
Range	Formula
F2:F7	F2	=IFERROR(MATCH(TRUE,B2:E2<A2:D2,0),5)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[motilulla]

It sounds then that the increasing values must start in thee first column. If that is so, try
(Looks to me like your third example result is incorrect since 4-7-12 is increasing)

24 02 26.xlsm
	A	B	C	D	E	F
1	n1	n2	n3	n4	n5	Ans
2	4	7	8	10	9	4
3	4	7	18	10	9	3
4	4	7	12	10	29	3
5	4	7	8	10	39	5
6	15	12	10	8	7	1
7	4	7	6	8	9	2
Ascending

Cell Formulas
Range	Formula
F2:F7	F2	=IFERROR(MATCH(TRUE,B2:E2<A2:D2,0),5)
Press CTRL+SHIFT+ENTER to enter array formulas.

Hello Peter_SSs, this is what I wanted worked beautifully. Thank you for your help.

Good Luck.

Kind Regards,
Moti

 

[motilulla]

Hello Peter_SSs, this is what I wanted worked beautifully. Thank you for your help.

Good Luck.

Kind Regards,
Moti

Hello Peter_SSs, your given formula according to my opening post is working fine.

I have come up with another problem adding duplicate data; formula also is giving the correct results as long as numbers are in ascending order.

My request is if there are duplicate can I get answer 0. Please can you take a look and make it possible new request.

Book1.xlsx
	A	B	C	D	E	F	G	H
1	n1	n2	n3	n4	n5	Ans		Ans
2	4	7	8	10	9	4		ok
3	4	7	18	10	9	3		ok
4	4	7	12	10	29	3		ok
5	4	7	8	10	39	5		ok
6	15	12	10	8	7	1		ok
7	4	7	6	8	9	2		ok
8
9						Ans		Ans
10	21	21	21	31	41	5		0
11	21	21	22	34	45	5		0
12	21	22	22	35	41	5		0
13	21	24	24	39	44	5		0
14	21	21	21	21	49	5		0
15	26	26	29	29	48	5		0
16	21	22	34	34	45	5		0
Sheet1

Cell Formulas
Range	Formula
F10:F16,F2:F7	F2	=IFERROR(MATCH(TRUE,B2:E2<A2:D2,0),5)
Press CTRL+SHIFT+ENTER to enter array formulas.

Good Luck.

Kind Regards,
Moti

 

[Peter_SSs]

My request is if there are duplicate can I get answer 0

Clarification needed. In all of your examples in the bottom sections thee number are increasing or equal as you go from left to right & the original formula gave a "5" result.
What about examples like the ones below where the original formula gives a different answer? Do you want these to return 0 as well or keep the original result or give yet another result?

24 02 26.xlsm
	A	B	C	D	E	F
18	21	22	18	18	45	2
19	21	22	22	18	45	3
Ascending (2)

Cell Formulas
Range	Formula
F18:F19	F18	=IFERROR(MATCH(TRUE,B18:E18<A18:D18,0),5)

 

[motilulla]

Clarification needed.

Hello Peter_SSs, thank you for asking the question. I have amended all data including your example I want the result as shown in the column F.

Give me counts of the numbers are in ascending order. But if the row contains duplicate numbers, than result must be 0.

For example in the row 2, 3, 4, 5, 6 & 7 there are NO DUPLICATE so far give count of the numbers are in ascending order as it gives in range F2:F7 are correct. If there are DUPLICATING in the row than return “0” as shown in the range F8:F16. Hope this help.

Book1.xlsx
	A	B	C	D	E	F
1	n1	n2	n3	n4	n5	Ans
2	4	7	8	10	9	4
3	4	7	18	10	9	3
4	4	7	12	10	29	3
5	4	7	8	10	39	5
6	15	12	10	8	7	1
7	4	7	6	8	9	2
8	21	21	21	31	41	0
9	21	21	22	34	45	0
10	21	22	22	35	41	0
11	21	24	24	39	44	0
12	21	21	21	21	49	0
13	26	26	29	29	48	0
14	21	22	34	34	45	0
15	21	22	18	18	45	0
16	21	22	22	18	48	0
Sheet1

Kind Regards,
Moti