Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to ave. last 3 months
Hi,
I need to ave. the last 4 months of the year. I have this formula to do it: =AVERAGE(OFFSET(B2,,COUNTA(B2:L2),,-(MIN(COUNTA(B2:L2),4)))) The twist is that I need it to ave. every 3rd cell only example: a b c d e f g h i Volume Expense unit cost Volume Expense unit cost Volume Expense unit 124,664 $38,197.15 0.3064 149,972 $46,902.63 0.3127 163,161 $50,837.560.3116 I would like to ave the volume (meaning column a,d,g) Thank you, Amit. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to ave. last 3 months
Amit,
You could do this by inserting a new row 1, and using a formula like this in cell A1: Your subject line says 3 months, your message says 4 month, so I used 4 - change the 4 to whatever is correct: =IF(A2="Volume",IF(COUNTIF(A2:$IV$2,"Volume")<=4," Sum",""),"") Copy that to fill row 1, then use the formula =SUMIF(B1:L1,"Sum",B3:L3)/4 to get the average.... HTH, Bernie MS Excel MVP wrote in message ps.com... Hi, I need to ave. the last 4 months of the year. I have this formula to do it: =AVERAGE(OFFSET(B2,,COUNTA(B2:L2),,-(MIN(COUNTA(B2:L2),4)))) The twist is that I need it to ave. every 3rd cell only example: a b c d e f g h i Volume Expense unit cost Volume Expense unit cost Volume Expense unit 124,664 $38,197.15 0.3064 149,972 $46,902.63 0.3127 163,161 $50,837.560.3116 I would like to ave the volume (meaning column a,d,g) Thank you, Amit. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to ave. last 3 months
Assuming that A1:L1 contains the header (Volume, Expense, Unit Cost,
Volume, Expense, Unit cost, etc.), and A2:L2 contains the corresponding value, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =AVERAGE(IF(A1:L1="Volume",IF(COLUMN(A2:L2)=LARGE (IF(A1:L1="Volume",COLU MN(A2:L2)),MIN(4,COUNTIF(A1:L1,"Volume"))),A2:L2)) ) Hope this helps! In article om, wrote: Hi, I need to ave. the last 4 months of the year. I have this formula to do it: =AVERAGE(OFFSET(B2,,COUNTA(B2:L2),,-(MIN(COUNTA(B2:L2),4)))) The twist is that I need it to ave. every 3rd cell only example: a b c d e f g h i Volume Expense unit cost Volume Expense unit cost Volume Expense unit 124,664 $38,197.15 0.3064 149,972 $46,902.63 0.3127 163,161 $50,837.560.3116 I would like to ave the volume (meaning column a,d,g) Thank you, Amit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use months in formula. | Excel Worksheet Functions | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
need if formula for 12 months | Excel Worksheet Functions | |||
Formula: Date plus 3 months | Excel Discussion (Misc queries) | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) |