Calculate average...

  • Thread starter Thread starter Mr-Wizard
  • Start date Start date
M

Mr-Wizard

Hi guys,

I want to calculate the average based on some sort of skipping rule.

I have a spreadsheet containing over 17000 rows and I want to calculat
the average based on a starting row, then a specified number of rows t
include, then specifying how many rows to skip until the process i
repeated.

For instance:

B
1 Use Value
2 Use Value
3 Use Value
4 Skip
5 Skip
6 Use Value
7 Use Value
8 Use Value
9 Skip
10 Skip
11 Use Value
12 Use Value
13 Use Value
14 Skip
15 Skip

Finally the average of all the 'Used Values' would be calculated.
The formula will need to be re-used with different parameters i.e
start at row 2, use the next 5 values, skip 2 rows, use the next
values, skip 2 rows etc...

I hope this makes sense

Many Thank
 
Hi
try the following with:
B1: start row
C1: number of used rows
D1: number of skipped rows
A1:A20000: values to sum

=SUMPRODUKT(--(ROW(A1:A20000)>=B1),--(MOD(ROW(A1:A20000)-B1,C1+D1)<C1),
A1:A20000)/SUMPRODUKT(--(ROW(A1:A20000)>=B1),--(MOD(ROW(A1:A20000)-B1,C
1+D1)<C1))
 
A cheesy workaround to a big formula would be;

if you have your values in A1:A1000, establish the pattern on the first 5
cells B1:B5 with the formula;
B1 =A1
B2 =A2
B3 =A3
B4 (leave blank)
B5 (leave blank)

then select B1:B5 (includes the 2 blanks), and drag it down through B1000.
Then just use =Average(b1:b1000)
 
Frank Kabel said:
try the following with:
B1: start row
C1: number of used rows
D1: number of skipped rows
A1:A20000: values to sum
Translating
=SUMPRODUCT(--(ROW(A1:A20000)>=B1),--(MOD(ROW(A1:A20000)-B1,C1+D1)<C1),
A1:A20000)/SUMPRODUCT(--(ROW(A1:A20000)>=B1),--(MOD(ROW(A1:A20000)-B1,
C1+D1)<C1))
Click to expand...

There are times when it makes sense to use array formulas.

=AVERAGE(IF((ROW(A1:A20000)>=B1)*(MOD(ROW(A1:A20000)-B1,C1+D1)<C1),
A1:A20000))
 
Harlan said:
There are times when it makes sense to use array formulas.

=AVERAGE(IF((ROW(A1:A20000)>=B1)*(MOD(ROW(A1:A20000)-B1,C1+D1)<C1),
A1:A20000))
Click to expand...

agreed :-)
Frank
 
Back
Top