Count Consecutive Numbers in a Row
 

Hi All,

Can anyone help me with a Formula to COUNT how many numbers are consecutive
within a Row that spans 10 Columns?

Example:
65 67 68 69 75 79 80 84 85 90

The answer to the above example should be a Count of 7.

Much appreciated.

Regards,
Sam

--
Message posted via http://www.officekb.com

I may have just misread this, but how do you get 7? I read consecutive as
increments of 1?

67/68 = 1
68/69 = 1
79/80 = 1
84/85 = 1
---
4

Assuming data in A1:J1

=SUMPRODUCT(--((B1:J1)-(A1:I1)=1))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Sam via OfficeKB.com" wrote in message
...
Hi All,

Can anyone help me with a Formula to COUNT how many numbers are
consecutive
within a Row that spans 10 Columns?

Example:
65 67 68 69 75 79 80 84 85 90

The answer to the above example should be a Count of 7.

Much appreciated.

Regards,
Sam

--
Message posted via http://www.officekb.com

Hi Ken,

Thanks for reply. Apologies for the confusion.

Yes, I agree, consecutives are increments of 1.

I should have elaborated further to say that I require a total count of the
individual numbers that make up the consecutive. So, rather than 67/68 = 1
or 79/80 = 1.

I need 67,68,69 = 3
79,80 = 2
84,85 = 2
---
Total Count 7

Example:
65 67 68 69 75 79 80 84 85 90

Is there a way that a Formula can be put together to Count the above and
arrive at total Count = 7?

Any further assistance much appreciated.

Regards,
Sam

--
Message posted via http://www.officekb.com

=count([Range]) where [Range] is your range.


--
nbrcrunch

To simplify the formulas, start your 10 numbers in column B or farther to the
right.

Assuming your numbers are in B1:K1, in B2 put this formula:

=IF(OR(B1=A1+1,B1=C1-1),1,0)

and copy it across through K1. The number of consecutive entries =SUM(B2:K2)

If your data is in A1:J1, you can't use the above formula in A2 because
there's no cell to the left of A1. The formulas for B2:J2 would be as above;
the formula for A2 would be

=IF(A1=B1-1,1,0)



On Fri, 18 Feb 2005 17:35:35 GMT, "Sam via OfficeKB.com"
wrote:

Hi Ken,

Thanks for reply. Apologies for the confusion.

Yes, I agree, consecutives are increments of 1.

I should have elaborated further to say that I require a total count of the
individual numbers that make up the consecutive. So, rather than 67/68 = 1
or 79/80 = 1.

I need 67,68,69 = 3
79,80 = 2
84,85 = 2
---
Total Count 7

Example:
65 67 68 69 75 79 80 84 85 90

Is there a way that a Formula can be put together to Count the above and
arrive at total Count = 7?

Any further assistance much appreciated.

Regards,
Sam

Hi Myrna,

Thank you very much for all your help - most appreciated.
Your suggested Formula did the job.

Regards,
Sam

--
Message posted via http://www.officekb.com