Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
OFFSET Formula
I am working with an OFFSET formula that was provided to me to help move
vertical data to horizontal data. In the original formula, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) , I was moving 6 sets of vertical data. Now I have 8 and the formula is not working. I am not sure how to manipulate the numbers. I am assuming the "*6-6 refers to the 6 sets of data and so I should change that to "*8-8", but I am not sure what the "-18" stands for. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
OFFSET Formula
Where is the data that is being transposed, and where are the formulas?
-- HTH Bob "Kiley" wrote in message ... I am working with an OFFSET formula that was provided to me to help move vertical data to horizontal data. In the original formula, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) , I was moving 6 sets of vertical data. Now I have 8 and the formula is not working. I am not sure how to manipulate the numbers. I am assuming the "*6-6 refers to the 6 sets of data and so I should change that to "*8-8", but I am not sure what the "-18" stands for. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
OFFSET Formula
Kiley,
=OFFSET($F$1,ROW()*6-6+COLUMN()-18,) Column()-18 If the formula is in Column R (Which is col 18) then the above returns zero. If the formula is in any cell to the left of col R it will return #REF because it will be a minus value. Basically this tells the formula where to start in the column of data. make it evaluate to zero and it will start on row 1. So a reasonable alternative to the formula would be I prefer this because it can now go in any column or row to return F1 and if we drag down 1 row it becomes =OFFSET($F$1,ROW(A2)*6-6+COLUMN(R2)-18,) and returns F7 If we wanted the formula to start on row 2 we would use =OFFSET($F$1,ROW(A1)*6-6+COLUMN(R1)-17,) So I think you now want =OFFSET($F$1,ROW(A1)*8-8+COLUMN(R1)-18,) Which returns f1 then f9 when dragged down -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kiley" wrote: I am working with an OFFSET formula that was provided to me to help move vertical data to horizontal data. In the original formula, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) , I was moving 6 sets of vertical data. Now I have 8 and the formula is not working. I am not sure how to manipulate the numbers. I am assuming the "*6-6 refers to the 6 sets of data and so I should change that to "*8-8", but I am not sure what the "-18" stands for. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
OFFSET Formula
Mike,
Thank you so much. That worked. :) "Mike H" wrote: Kiley, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) Column()-18 If the formula is in Column R (Which is col 18) then the above returns zero. If the formula is in any cell to the left of col R it will return #REF because it will be a minus value. Basically this tells the formula where to start in the column of data. make it evaluate to zero and it will start on row 1. So a reasonable alternative to the formula would be I prefer this because it can now go in any column or row to return F1 and if we drag down 1 row it becomes =OFFSET($F$1,ROW(A2)*6-6+COLUMN(R2)-18,) and returns F7 If we wanted the formula to start on row 2 we would use =OFFSET($F$1,ROW(A1)*6-6+COLUMN(R1)-17,) So I think you now want =OFFSET($F$1,ROW(A1)*8-8+COLUMN(R1)-18,) Which returns f1 then f9 when dragged down -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kiley" wrote: I am working with an OFFSET formula that was provided to me to help move vertical data to horizontal data. In the original formula, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) , I was moving 6 sets of vertical data. Now I have 8 and the formula is not working. I am not sure how to manipulate the numbers. I am assuming the "*6-6 refers to the 6 sets of data and so I should change that to "*8-8", but I am not sure what the "-18" stands for. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
OFFSET Formula
Glad I could help
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kiley" wrote: Mike, Thank you so much. That worked. :) "Mike H" wrote: Kiley, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) Column()-18 If the formula is in Column R (Which is col 18) then the above returns zero. If the formula is in any cell to the left of col R it will return #REF because it will be a minus value. Basically this tells the formula where to start in the column of data. make it evaluate to zero and it will start on row 1. So a reasonable alternative to the formula would be I prefer this because it can now go in any column or row to return F1 and if we drag down 1 row it becomes =OFFSET($F$1,ROW(A2)*6-6+COLUMN(R2)-18,) and returns F7 If we wanted the formula to start on row 2 we would use =OFFSET($F$1,ROW(A1)*6-6+COLUMN(R1)-17,) So I think you now want =OFFSET($F$1,ROW(A1)*8-8+COLUMN(R1)-18,) Which returns f1 then f9 when dragged down -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kiley" wrote: I am working with an OFFSET formula that was provided to me to help move vertical data to horizontal data. In the original formula, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) , I was moving 6 sets of vertical data. Now I have 8 and the formula is not working. I am not sure how to manipulate the numbers. I am assuming the "*6-6 refers to the 6 sets of data and so I should change that to "*8-8", but I am not sure what the "-18" stands for. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
OFFSET Formula
If I may ask you another question. If I have a set of veritcal data for
each employee, but each set of data could range from 1 to 4 vertical values, is there a way to change the following formulas to accomodate moving the data from horizontal to vertical?: =INDEX($A$2:$A$1690,1+(8*(ROWS(H$1:H1)-1))) =OFFSET($G$2,ROW(A1)*8-8+COLUMN(R2)-18,) Data Sample: ID Data 275 Capitalize on Technical and Professional Know-How 275 Foster Teamwork 275 Value Others 933 Demonstrate Agility 933 Drive for Results 933 Team Leadership 933 Use Technical/Functional Expertise 658 Capitalize on Technical and Professional Know-How 658 Foster Teamwork 658 Manage Execution 1113 Demonstrate Drive & Commitment 1113 Leading Innovation 1113 Maximize Empowerment 147 Align with Strategy & Direction 147 Foster Teamwork 147 Manage for Results 1685 Analyze/Solve Problems 1685 Demonstrate Drive & Commitment 1685 Meet Customer Needs 880 Foster Teamwork 880 Manage for Results 880 Value Others 1130 Commit to Quality 1130 Develop Oneself 1130 Prepare Written Communication 227 Demonstrate Adaptability 227 Foster Open Communication 227 Innovate 267 Coach Others 267 Lead Boldly 267 Leading Change 253 Make Sound Decisions 253 Manage for Results 1521 Build Relationships "Mike H" wrote: Glad I could help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kiley" wrote: Mike, Thank you so much. That worked. :) "Mike H" wrote: Kiley, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) Column()-18 If the formula is in Column R (Which is col 18) then the above returns zero. If the formula is in any cell to the left of col R it will return #REF because it will be a minus value. Basically this tells the formula where to start in the column of data. make it evaluate to zero and it will start on row 1. So a reasonable alternative to the formula would be I prefer this because it can now go in any column or row to return F1 and if we drag down 1 row it becomes =OFFSET($F$1,ROW(A2)*6-6+COLUMN(R2)-18,) and returns F7 If we wanted the formula to start on row 2 we would use =OFFSET($F$1,ROW(A1)*6-6+COLUMN(R1)-17,) So I think you now want =OFFSET($F$1,ROW(A1)*8-8+COLUMN(R1)-18,) Which returns f1 then f9 when dragged down -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kiley" wrote: I am working with an OFFSET formula that was provided to me to help move vertical data to horizontal data. In the original formula, =OFFSET($F$1,ROW()*6-6+COLUMN()-18,) , I was moving 6 sets of vertical data. Now I have 8 and the formula is not working. I am not sure how to manipulate the numbers. I am assuming the "*6-6 refers to the 6 sets of data and so I should change that to "*8-8", but I am not sure what the "-18" stands for. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can offset be used in this formula? | Excel Discussion (Misc queries) | |||
Offset formula | Excel Worksheet Functions | |||
Offset Formula | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset formula | Excel Worksheet Functions |