averaging 32 fields

T

tee

HP6=AVERAGE(H6,N6,U6,AB6,AI6,AP6,AW6,BD6,BK6,BR6,BY6,CF6,CM6,CT6,DA6,DH6,DO6,DV6,EC6,EJ6,EQ6,EX6,FE6,FL6,FS6,FZ6,GG6,GN6,GU6,HB6,HI6,)

The program will not let me include HB6 and HI6. I have to stop at GU6
because I can only average 30 fields.

I recently asked this question and Bif told me to post the formula.

Thanks
 
N

Niek Otten

Set up a range (for example B1 to B32):

=H6
=N6
=U6

etc

Then use =AVERAGE(B1:B32)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
HP6=AVERAGE(H6,N6,U6,AB6,AI6,AP6,AW6,BD6,BK6,BR6,BY6,CF6,CM6,CT6,DA6,DH6,DO6,DV6,EC6,EJ6,EQ6,EX6,FE6,FL6,FS6,FZ6,GG6,GN6,GU6,HB6,HI6,)
|
| The program will not let me include HB6 and HI6. I have to stop at GU6
| because I can only average 30 fields.
|
| I recently asked this question and Bif told me to post the formula.
|
| Thanks
 
R

RagDyeR

It's best if you stay in the original thread so others can see what has
previously been suggested.

Anyway, do you have a typo, or is it intentional that you start off with 6
columns, and then switch to every 7 columns?

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------



HP6=AVERAGE(H6,N6,U6,AB6,AI6,AP6,AW6,BD6,BK6,BR6,BY6,CF6,CM6,CT6,DA6,DH6,DO6
,DV6,EC6,EJ6,EQ6,EX6,FE6,FL6,FS6,FZ6,GG6,GN6,GU6,HB6,HI6,)

The program will not let me include HB6 and HI6. I have to stop at GU6
because I can only average 30 fields.

I recently asked this question and Bif told me to post the formula.

Thanks
 
B

Biff

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=AVERAGE(IF(MOD(COLUMN(N6:HI6),7)=0,N6:HI6),H6)

The column interval is different after H6 as RD noted.

Biff
 
H

Harlan Grove

tee wrote...
HP6
=AVERAGE(H6,N6,U6,AB6,AI6,AP6,AW6,BD6,BK6,BR6,BY6,CF6,CM6,CT6,
DA6,DH6,DO6,DV6,EC6,EJ6,EQ6,EX6,FE6,FL6,FS6,FZ6,GG6,GN6,GU6,HB6,HI6)

The program will not let me include HB6 and HI6. I have to stop at GU6
because I can only average 30 fields.
Click to expand...
....

So make it one multiple area range.

=AVERAGE((H6,N6,U6,AB6,AI6,AP6,AW6,BD6,BK6,BR6,BY6,CF6,CM6,CT6,
DA6,DH6,DO6,DV6,EC6,EJ6,EQ6,EX6,FE6,FL6,FS6,FZ6,GG6,GN6,GU6,HB6,HI6))
 
T

Tushar Mehta

HP6=AVERAGE(H6,N6,U6,AB6,AI6,AP6,AW6,BD6,BK6,BR6,BY6,CF6,CM6,CT6,DA6,DH6,DO6,DV6,EC6,EJ6,EQ6,EX6,FE6,FL6,FS6,FZ6,GG6,GN6,GU6,HB6,HI6,)

The program will not let me include HB6 and HI6. I have to stop at GU6
because I can only average 30 fields.

I recently asked this question and Bif told me to post the formula.

Thanks
Click to expand...
=AVERAGE
((H6,N6,U6,AB6,AI6,AP6,AW6,BD6,BK6,BR6,BY6,CF6,CM6,CT6,DA6,DH6,DO6,DV6,EC6,E
J6,EQ6,EX6,FE6,FL6,FS6,FZ6,GG6,GN6,GU6,HB6,HI6))

AVERAGE accepts only 30 arguments but each argument can consist of multiple
discontiguous ranges. That's what the 2nd set of parenthesis does. It
tells AVERAGE there is only one argument and it contains so many
discontiguous ranges.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top