| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average based on the previous row
Hi All,
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 please help. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average based on the previous row
=SUMPRODUCT(--(MONTH(B2:AF2)=1),--(B3:AF3))
"Gary" wrote: Hi All, 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 please help. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average based on the previous row
Try this:
=AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3)) This is an array formula, so once you have typed it in (or subsequently edit it) you should commit it with CTRL-SHIFT-ENTER (CSE) instead of just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. Change the 1 to a 2 for February, etc. Hope this helps. Pete On Feb 5, 10:53 pm, "Gary" wrote: Hi All, 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 please help. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average based on the previous row
Try this:
I'm assuming there are no empty cells in row 2. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(MONTH(B2:FZ2)=1,B3:FZ3)) Biff "Gary" wrote in message ... Hi All, 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 please help. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average based on the previous row
Hi Gary
One way =SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<"")*B3:AF3)/ SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<"")) -- Regards Roger Govier "Gary" wrote in message ... Hi All, 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 please help. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average based on the previous row
Thanks a lot all of you.
:) "Roger Govier" wrote in message ... Hi Gary One way =SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<"")*B3:AF3)/ SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<"")) -- Regards Roger Govier "Gary" wrote in message ... Hi All, 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 please help. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formatting cell number based on previous cell number | Excel Discussion (Misc queries) | |||
| calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
| conditional cell format based on cell in same row, previous column | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
Linear Mode
