Calculate Average for same period last year

[irish_hp]

I need to calculate the average rate both Year To Date and for the same
period last year. I have two rows of data.

Row 1 shows Current Year average rates per month (all zeros after previous
month; ie April - December are currently zero)
Row 2 shows Last Year average rates per month (all months have positive
values).

I've used the formula =SUM(C35:N35)/(COUNTIF(C35:N35,"<>0")) to get the
average rate for the Current Year but, I also want to be able to
automatically calculate the average rate for the same period last year (ie
Jan - March only in this example).

 

[Mike H]

Try this

=SUM(IF(C35:N35<>0,C36:N36,0),0)/(COUNTIF(C35:N35,"<>0"))

It's an array so enter with CTRL+Shift+Enter. I have assumed last yesr is
one row down from this year.

Mike

 

[irish_hp]

Thanks Mike H, that works perfectly.

Try this

=SUM(IF(C35:N35<>0,C36:N36,0),0)/(COUNTIF(C35:N35,"<>0"))

It's an array so enter with CTRL+Shift+Enter. I have assumed last yesr is
one row down from this year.

Mike

 

[Mike H]

Your welcome and thank you for the feedback

Thanks Mike H, that works perfectly.