Number of arguments

[Weather Consultancy Services]

Hi,

I am entering maximum temperatures each day and want to calculate the
average of these temperatures.

Not all days are available so I only want the average of those days when
temperatures are available.

All is fine when using =AVERAGEA in a month when there are 30 days, but when
there are 31 Excel won't calculate this as the number of arguments is
limited to 30 (I am calculating from data on Shet 1 to Sheet 2).

Is there anyway to work around this?

Thanks,
Simon

--
Weather Consultancy Services / Weather School
The Weather Centre, 188 Common Road, Wombourne, South Staffordshire. WV5
0LT.
Tel: 01902 895252
email: (e-mail address removed)

http://www.weatherweb.net
http://www.weatherschool.co.uk
http://www.atlanticweather.co.uk

 

[RagDyeR]

Are you entering your arguments on an individual cell basis?

You can group your arguments into ranges, which are treated as individual
arguments by Average().

So, enter ranges of days where the temperatures are available, and eliminate
cells where they are absent:

=AVERAGE(A1:A5,A7:A10,A12,A14:A31)

--

HTH,

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


Hi,

I am entering maximum temperatures each day and want to calculate the
average of these temperatures.

Not all days are available so I only want the average of those days when
temperatures are available.

All is fine when using =AVERAGEA in a month when there are 30 days, but when
there are 31 Excel won't calculate this as the number of arguments is
limited to 30 (I am calculating from data on Shet 1 to Sheet 2).

Is there anyway to work around this?

Thanks,
Simon

--
Weather Consultancy Services / Weather School
The Weather Centre, 188 Common Road, Wombourne, South Staffordshire. WV5
0LT.
Tel: 01902 895252
email: (e-mail address removed)

http://www.weatherweb.net
http://www.weatherschool.co.uk
http://www.atlanticweather.co.uk

 

[Weather Consultancy Services]

HI RD,

Thanks for that. Unfortunately, the spreadsheet in't conducive to that I
don't think. It's laid out as follows
COLUMN1 = Max temp
COLUMN2 = Min temp
COLUMN 3= Rain

The ROWS are individual weather stations and then the days are in the
columns above. Hope that makes sense.

Thanks for your help,
Simon

 

[RagDyeR]

Not being overly bright<g>, I don't follow exactly where your individual
days are located on your list.

First 3 columns are Max, Min, and Rain.

Then, rows are individual stations.

Are the days then entered in 31 columns *beyond* the 3rd column, from Column
D to Column AH, with the Average formula in maybe AI?

If that's the case, you might try this *array* formula, which will average a
range, *without* including blank cells. but *will* include zeroes:

=AVERAGE(IF(D2:AH2<>"",D2:AH2))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Adjust the ranges to suit, and copy down as needed.
--

HTH,

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

HI RD,

Thanks for that. Unfortunately, the spreadsheet in't conducive to that I
don't think. It's laid out as follows
COLUMN1 = Max temp
COLUMN2 = Min temp
COLUMN 3= Rain

The ROWS are individual weather stations and then the days are in the
columns above. Hope that makes sense.

Thanks for your help,
Simon

 

[RagDyeR]

Just confirming what I said in my last post about not being overly bright.

The Average function *doesn't* include blanks in the average calculation by
design.

Have you tried it?

I realized my formula was originally designed to not count zeroes, but for
your purposes,

=AVERAGE(D2:AH2)

Should work fine.

You'll have to pardon me, I'm not yet on my second pot of coffee.
--

Regards,

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

Not being overly bright<g>, I don't follow exactly where your individual
days are located on your list.

First 3 columns are Max, Min, and Rain.

Then, rows are individual stations.

Are the days then entered in 31 columns *beyond* the 3rd column, from Column
D to Column AH, with the Average formula in maybe AI?

If that's the case, you might try this *array* formula, which will average a
range, *without* including blank cells. but *will* include zeroes:

=AVERAGE(IF(D2:AH2<>"",D2:AH2))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Adjust the ranges to suit, and copy down as needed.
--

HTH,

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

HI RD,

Thanks for that. Unfortunately, the spreadsheet in't conducive to that I
don't think. It's laid out as follows
COLUMN1 = Max temp
COLUMN2 = Min temp
COLUMN 3= Rain

The ROWS are individual weather stations and then the days are in the
columns above. Hope that makes sense.

Thanks for your help,
Simon

--

 

[Weather Consultancy Services]

Thanks again RD,

Not sure if that will work either.

The locations are C4, I4, O4 etc...

Does this help?

Simon

 

[RagDyer]

That means that 31 days at that interval (every 6 columns) goes out to GA4
.... right?

Try this formula:

=SUMPRODUCT((MOD(COLUMN(C4:GA4)+3,6)=0)*(C4:GA4))/SUMPRODUCT((MOD(COLUMN(C4:
GA4)+3,6)=0)*(C4:GA4<>""))

Copy down as needed, and watch out for word wrap.
--
HTH,

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