i want to calculate the frequency of a set of numbers in a column that are
spaced 24 rows apart, i.e. want to calculate the frequency for the following
values, A2,A26,A50,A74...etc..

Use this in say, B2:
=INDIRECT("A"&ROWS($1:1)*24-22)
Copy B2 down as far as required to extract all the values (A2, A26, etc)
into a contiguous col range. Then you could easily do whatever you want.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Reza" wrote:
i want to calculate the frequency of a set of numbers in a column that are
spaced 24 rows apart, i.e. want to calculate the frequency for the following
values, A2,A26,A50,A74...etc..

i want to calculate the frequency

Define frequency.

This formula will return the count of 1's in the range A2, A26, A50, A74:

=SUMPRODUCT(--(MOD(ROW(A2:A74),24)=2),--(A2:A74=1))

Or, this version will account for new row insertions above the range:

=SUMPRODUCT(--(MOD(ROW(A2:A74)-ROW(A2),24)=0),--(A2:A74=1))


--
Biff
Microsoft Excel MVP


"Reza" wrote in message
...
i want to calculate the frequency of a set of numbers in a column that are
spaced 24 rows apart, i.e. want to calculate the frequency for the
following
values, A2,A26,A50,A74...etc..

On Apr 10, 10:16*pm, "T. Valko" wrote:
i want to calculate the frequency

Define frequency.

This formula will return the count of 1's in the range A2, A26, A50, A74:

=SUMPRODUCT(--(MOD(ROW(A2:A74),24)=2),--(A2:A74=1))

Or, this version will account for new row insertions above the range:

=SUMPRODUCT(--(MOD(ROW(A2:A74)-ROW(A2),24)=0),--(A2:A74=1))

--
Biff
Microsoft Excel MVP

"Reza" wrote in message

...



i want to calculate the frequency of a set of numbers in a column that are
spaced 24 rows apart, i.e. want to calculate the frequency for the
following
values, A2,A26,A50,A74...etc..- Hide quoted text -

- Show quoted text -

Beauty!!!!!!!!!!!!!!! thank
you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

On Apr 10, 8:53*pm, Max wrote:
Use this in say, B2:
=INDIRECT("A"&ROWS($1:1)*24-22)
Copy B2 down as far as required to extract all the values (A2, A26, etc)
into a contiguous col range. Then you could easily do whatever you want.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---



"Reza" wrote:
i want to calculate the frequency of a set of numbers in a column that are
spaced 24 rows apart, i.e. want to calculate the frequency for the following
values, A2,A26,A50,A74...etc..- Hide quoted text -

- Show quoted text -

THANK YOU!!!!

You're welocme. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
On Apr 10, 10:16 pm, "T. Valko" wrote:
i want to calculate the frequency

Define frequency.

This formula will return the count of 1's in the range A2, A26, A50, A74:

=SUMPRODUCT(--(MOD(ROW(A2:A74),24)=2),--(A2:A74=1))

Or, this version will account for new row insertions above the range:

=SUMPRODUCT(--(MOD(ROW(A2:A74)-ROW(A2),24)=0),--(A2:A74=1))

--
Biff
Microsoft Excel MVP

"Reza" wrote in message

...



i want to calculate the frequency of a set of numbers in a column that
are
spaced 24 rows apart, i.e. want to calculate the frequency for the
following
values, A2,A26,A50,A74...etc..- Hide quoted text -

- Show quoted text -

Beauty!!!!!!!!!!!!!!! thank
you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Welcome ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote
THANK YOU!!!!