Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Number of arguments
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: http://www.weatherweb.net http://www.weatherschool.co.uk http://www.atlanticweather.co.uk |
#2
|
|||
|
|||
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! ================================================== === "Weather Consultancy Services" wrote in message ... 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: http://www.weatherweb.net http://www.weatherschool.co.uk http://www.atlanticweather.co.uk |
#3
|
|||
|
|||
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" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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 |
#4
|
|||
|
|||
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! ================================================== === "Weather Consultancy Services" wrote in message ... 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" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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 |
#5
|
|||
|
|||
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 ! ---------------------------------------------------------------------------- ------------------- "RagDyeR" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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 |
#6
|
|||
|
|||
Thanks again RD,
Not sure if that will work either. The locations are C4, I4, O4 etc... Does this help? Simon "RagDyeR" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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 |
#7
|
|||
|
|||
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! ============================================== "Weather Consultancy Services" wrote in message ... Thanks again RD, Not sure if that will work either. The locations are C4, I4, O4 etc... Does this help? Simon "RagDyeR" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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" wrote in message ... 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! ================================================== === "Weather Consultancy Services" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
doubling a number X number of times | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
How do I sort a column a unique number? | Excel Worksheet Functions |