averaging 32 fields

[Guest]

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

 

[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

 

[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

 

[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

 

[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.

....

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))

 

[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

=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