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

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

"tee" <(E-Mail Removed)> wrote in message news:5605EB72-B863-493D-84C8-(E-Mail Removed)...
|
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

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



"tee" <(E-Mail Removed)> wrote in message
news:5605EB72-B863-493D-84C8-(E-Mail Removed)...
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

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

"tee" <(E-Mail Removed)> wrote in message
news:5605EB72-B863-493D-84C8-(E-Mail Removed)...
> 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

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

In article <5605EB72-B863-493D-84C8-(E-Mail Removed)>,
(E-Mail Removed) says...
> 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