Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Transpose formula
In cell L10 in wksht2 is the formula:
=IF(ISERROR(AVERAGE(wksht1!M74:M85)),0,AVERAGE(wks ht1!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(wks ht1!M86:M97)) and the formula in cell L12 = =IF(ISERROR(AVERAGE(wksht1!M98:M109)),0,AVERAGE(wk sht1!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. |
#2
|
|||
|
|||
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 "SteveC" wrote in message ... In cell L10 in wksht2 is the formula: =IF(ISERROR(AVERAGE(wksht1!M74:M85)),0,AVERAGE(wks ht1!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(wks ht1!M86:M97)) and the formula in cell L12 = =IF(ISERROR(AVERAGE(wksht1!M98:M109)),0,AVERAGE(wk sht1!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. |
#3
|
|||
|
|||
Works great! Thanks...
"Biff" wrote: 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 "SteveC" wrote in message ... In cell L10 in wksht2 is the formula: =IF(ISERROR(AVERAGE(wksht1!M74:M85)),0,AVERAGE(wks ht1!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(wks ht1!M86:M97)) and the formula in cell L12 = =IF(ISERROR(AVERAGE(wksht1!M98:M109)),0,AVERAGE(wk sht1!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. |
#4
|
|||
|
|||
You're welcome! Thanks for the feedback.
Biff "SteveC" wrote in message ... Works great! Thanks... "Biff" wrote: 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 "SteveC" wrote in message ... In cell L10 in wksht2 is the formula: =IF(ISERROR(AVERAGE(wksht1!M74:M85)),0,AVERAGE(wks ht1!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(wks ht1!M86:M97)) and the formula in cell L12 = =IF(ISERROR(AVERAGE(wksht1!M98:M109)),0,AVERAGE(wk sht1!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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |