Average by year from a set of values (1 Viewer)

[musthu]

308.28 16-Oct-06
220.42 19-Dec-06
192.35 16-Aug-07
251.06 20-Sep-07
445.76 10-Dec-07
0 17-Dec-07
25.56 17-Jan-08
170.35 3-Feb-08
149.24 3-Feb-08

The Colum A contains set of values and B has the corresponding year for that value. I'd like to have the average value, minimum value and maximum value for each particular year.
like average for 2006=..
min value for 2006=..
max value for 2006=...
same for other years

 

[chergh]

I've done 2006 as an example for you. You can pretty much copy the code for other years but will obviously need to change the variable names for them. I've not tested the code so could be a couple of small issues with it.

dim ws as worksheet
dim ws1 as worksheet

dim 2006Total as long
dim 2006Count as long
dim 2006Max as long
dim 2006Min as long

set ws = thisworkbook.worksheets("SheetName")
set ws1 = thisworkbook.worksheets("OtherSheet")

2006Total = 0
2006Max = 0
2006Min = 0
2006Count = 0

for i = 1 to ws.range("A65536").end(xlup).row

select case datepart("yyyy", ws.range("B" & i).value)

case = 2006

2006Count = 2006Count + 1

2006Total = 2006Total + ws.range("A" & i).value

If 2006Max <  ws.range("A" & i).value Then
   2006Max = ws.range("A" & i).value
end if

If 2006Min = 0 Then
   2006Min = ws.range("A" & i).value
ElseIf 2006Min > ws.range("A" & i).value Then
   2006Min = ws.range("A" & i).value
End if

case = 2007

Case = 2008

end select

next i

ws1.range("A1").value = "2006 Average"
ws1.range("B1").value = 2006Total/2006Count

ws1.range("A2").value = "2006 Min"
ws1.range("B2").value = 2006Min

ws1.range("A3").value = "2006 Max"
ws1.range("B3").value = 2006max

 

[musthu]

Thanks
i am pretty much bad with coding any way let me give a try.

 

[whitespace]

There's no need to use coding for this mate if you're not used to it.

Instead you can use what's called an array formula to do a sort of AverageIF function.

Assuming your figures are in cells A1:A9 and the dates are in cells B1:B9, write 2006 in cell C15 and then in D15 write the following formula but afterwards ensure you press CTRL, SHIFT AND ENTER (Instead of just enter) - this will put curly brackets around the formula.

Here is the formala:
=AVERAGE(IF(YEAR(B1:B9)=C15,A1:A9))

Then you can write 2007 in C16 and just drag the formula down.

Does this make sense?

 

[musthu]

I tried with formula , but the result shown is zero. sample file attached. Is there a mistake in it? Thanks for your reply

 

[whitespace]

Hello Musthu, there were a couple of things wrong with that spreadsheet. First the years in column A were stored as dates and not numbers (so they wouldn't match to the year function). The second thing is that they weren't 'Array formulas' - to make them array formulas ensure you enter the formula and then instead of pressing enter, press CTRL, SHIFT AND ENTER. This will put curly brackets around the formula to show that it is an array formula.

I've sorted these issues out for you though - please see attached file. Let me know if any problems.

Cheers

 

[musthu]

Thanx for the corrections. it works perfectly now.