Transpose formula

[Guest]

In cell L10 in wksht2 is the formula:
=IF(ISERROR(AVERAGE(wksht1!M74:M85)),0,AVERAGE(wksht1!M74:M85))

I'd like to drag this from L10 to L20 so that the formula in cell L11=
=IF(ISERROR(AVERAGE(wksht1!M86:M97)),0,AVERAGE(wksht1!M86:M97))

and the formula in cell L12 =
=IF(ISERROR(AVERAGE(wksht1!M98:M109)),0,AVERAGE(wksht1!M98:M109))

and so on as you drag it right

Thanks very much.

Also,
I'm just curious how you would create solve a simple tranpose formula issue
so that I can transpose with links and by dragging across
A1
A2
A3

into something like A3, B3, C3

I'm not sure if the two questions are the same or not. Thanks very much.

 

[Biff]

Hi!

In cell L10 in wksht2 is the formula:
I'd like to drag this from L10 to L20
and so on as you drag it right

Enter this formula in L10 and drag down to L20:

=IF(ISERROR(AVERAGE(OFFSET(wksht1!M$74,(ROW(1:1)-1)*12,,12))),0,AVERAGE(OFFSET(wksht1!M$74,(ROW(1:1)-1)*12,,12)))

Then, if you drag it across you'll end up with the same range averages as
they appy to column(s) N, O, P etc,.

Biff

 

[Guest]

Works great! Thanks...

Hi!


Enter this formula in L10 and drag down to L20:

=IF(ISERROR(AVERAGE(OFFSET(wksht1!M$74,(ROW(1:1)-1)*12,,12))),0,AVERAGE(OFFSET(wksht1!M$74,(ROW(1:1)-1)*12,,12)))

Then, if you drag it across you'll end up with the same range averages as
they appy to column(s) N, O, P etc,.

Biff

 

[Biff]

You're welcome! Thanks for the feedback.

Biff

Works great! Thanks...