| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Query
Hi All....I posted the following query few days back and got a couple of
answers. But the problem is none of'em seem to work. All the formulas that you guys suggested giving me a VALUE error. The formulae suggested by few of you were.... =SUMPRODUCT(--(MONTH(B2:AF2)=1),--(B3:AF3)) =AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3)) (array formula) =SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<"")*B3:AF3)/ SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<"")) please go through the following query once again and suggest some better solutions. Thanks a ton. in row 2 I have dates jan 1 to jun 31 in row 3 I have some data. now if in cell A60 i need the average of the data in row 3 only for January....so if Jan starts in cell B2 and ends in cell AF2, I need the average of data in B3 to AF3 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Query
Gary,
I respectfully ask you to look at your data. I tried all three formulae and all gave the expected and CORRECT results. Your value error is likely to be data. "Gary" wrote: Hi All....I posted the following query few days back and got a couple of answers. But the problem is none of'em seem to work. All the formulas that you guys suggested giving me a VALUE error. The formulae suggested by few of you were.... =SUMPRODUCT(--(MONTH(B2:AF2)=1),--(B3:AF3)) =AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3)) (array formula) =SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<"")*B3:AF3)/ SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<"")) please go through the following query once again and suggest some better solutions. Thanks a ton. in row 2 I have dates jan 1 to jun 31 in row 3 I have some data. now if in cell A60 i need the average of the data in row 3 only for January....so if Jan starts in cell B2 and ends in cell AF2, I need the average of data in B3 to AF3 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Query
Hi Toppers.
I have tried it...the formula AVERAGE(IF(MONTH($C$2:$Y$2)=1,$C$4:$Y$4)) works for January but not for any other month. if in the next column I write AVERAGE(IF(MONTH($C$2:$Y$2)=2,$C$4:$Y$4)), it should give me the Average of the data under Feb but it's not doing that. And even in January, what this formula is doing is Sum of all the populated cells in row 4 / number of days in january. that is not what i want..i want running average...for example...sum of populated cells in row 4 / count of populated cells....I dont it to take al the blank cells under January in to the account. Thanks Gary "Toppers" wrote in message ... Gary, I respectfully ask you to look at your data. I tried all three formulae and all gave the expected and CORRECT results. Your value error is likely to be data. "Gary" wrote: Hi All....I posted the following query few days back and got a couple of answers. But the problem is none of'em seem to work. All the formulas that you guys suggested giving me a VALUE error. The formulae suggested by few of you were.... =SUMPRODUCT(--(MONTH(B2:AF2)=1),--(B3:AF3)) =AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3)) (array formula) =SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<"")*B3:AF3)/ SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<"")) please go through the following query once again and suggest some better solutions. Thanks a ton. in row 2 I have dates jan 1 to jun 31 in row 3 I have some data. now if in cell A60 i need the average of the data in row 3 only for January....so if Jan starts in cell B2 and ends in cell AF2, I need the average of data in B3 to AF3 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Query
Gary wasn't using the figures past Jan so couldn't get Feb.
y2 should have been eb2 -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Please give an example of your data layout or send me a small workbook sample along with copies of ALL of this thread and a clear explanation of what you want. -- Don Guillett SalesAid Software "Gary" wrote in message ... Hi Toppers. I have tried it...the formula AVERAGE(IF(MONTH($C$2:$Y$2)=1,$C$4:$Y$4)) works for January but not for any other month. if in the next column I write AVERAGE(IF(MONTH($C$2:$Y$2)=2,$C$4:$Y$4)), it should give me the Average of the data under Feb but it's not doing that. And even in January, what this formula is doing is Sum of all the populated cells in row 4 / number of days in january. that is not what i want..i want running average...for example...sum of populated cells in row 4 / count of populated cells....I dont it to take al the blank cells under January in to the account. Thanks Gary "Toppers" wrote in message ... Gary, I respectfully ask you to look at your data. I tried all three formulae and all gave the expected and CORRECT results. Your value error is likely to be data. "Gary" wrote: Hi All....I posted the following query few days back and got a couple of answers. But the problem is none of'em seem to work. All the formulas that you guys suggested giving me a VALUE error. The formulae suggested by few of you were.... =SUMPRODUCT(--(MONTH(B2:AF2)=1),--(B3:AF3)) =AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3)) (array formula) =SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<"")*B3:AF3)/ SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<"")) please go through the following query once again and suggest some better solutions. Thanks a ton. in row 2 I have dates jan 1 to jun 31 in row 3 I have some data. now if in cell A60 i need the average of the data in row 3 only for January....so if Jan starts in cell B2 and ends in cell AF2, I need the average of data in B3 to AF3 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Query
If you want an AVERAGE forget about formula #1.
My preference would be to use formula #2, the array formula. You noted that it was an array formula BUT did you enter it as an array? Did you use the key combination of CTRL,SHIFT,ENTER (not just ENTER)? Are there any EMPTY cells within your DATE range? An EMPTY cell will evaluate as month 1 (although that won't cause a #VALUE! error) Are your dates true Excel dates or are they just TEXT strings? I'm guessing your problem is that the dates are not DATES but are actually TEXT strings. If B2 = Jan 1 What is the result of this formula: =ISNUMBER(B2) If the result is FALSE then your dates are TEXT strings and the error is being generated by the call to MONTH. Biff "Gary" wrote in message ... Hi All....I posted the following query few days back and got a couple of answers. But the problem is none of'em seem to work. All the formulas that you guys suggested giving me a VALUE error. The formulae suggested by few of you were.... =SUMPRODUCT(--(MONTH(B2:AF2)=1),--(B3:AF3)) =AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3)) (array formula) =SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<"")*B3:AF3)/ SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<"")) please go through the following query once again and suggest some better solutions. Thanks a ton. in row 2 I have dates jan 1 to jun 31 in row 3 I have some data. now if in cell A60 i need the average of the data in row 3 only for January....so if Jan starts in cell B2 and ends in cell AF2, I need the average of data in B3 to AF3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
| Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
| "Average" function query | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
Hybrid Mode
