Calculate Average for same period last year
 

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).

Calculate Average for same period last year
 

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" wrote:

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).

Calculate Average for same period last year
 

Thanks Mike H, that works perfectly.

"Mike H" wrote:

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" wrote:

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).

Calculate Average for same period last year
 

Your welcome and thank you for the feedback

"irish_hp" wrote:

Thanks Mike H, that works perfectly.

"Mike H" wrote:

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" wrote:

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).