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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]>
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

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


--
nbrcrunch

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