I’ve got a spreadsheet with one set of numbers that runs down a column that needs to be calculated in formula that runs across a row. For example,
Set 1 (Data Set):
…A…B…
.|
1|…2…3
2|…1…6
3|…5…4
.|
Set 2 (Calculation):
…A…B…C…
.|
1|]…A1+B1…A2+B2…A3+B3
.|
I know that if I ran the calculation vertically, I could put the formula (A1+B1) in the first cell, drag the corner down and the other cells would adjust automatically (A2+B2, A3+B3), but if I drag the first cell across, the column designation will change but not the row number (A1+B1, B1+C1, C1+D1).How do I make it so I can drag the cell across while the column in the formula stays the same and the row number increases?
Unfortunately I do not have Excel on this PC to test my theory, but you put a dollar sign in front of the location that you do not want to change. Ex. $A1 changes the row 1,2,3 etc, not the column whereas A$1 changes the column, A,B,C, etc, and not the row.
$A$1 will always refer to that cell.
Like I said, I am going on memory here. But give that a shot.
Drag it down first to get the numbers to increment, then cut the relevant part of the column, then “Paste special…” from the edit menu and choose “transpose” - it’ll paste the column horizontally.
Caveat: I don’t have Excel on this computer so can’t test this theory.
Result of a $ in front of the column letter of the first cell and dragging it right two cells:
…A…B…C…
.|
1|…$A1+$B1…$A1+$B1…$A1+$B1
.|
Result of a $ in front of the row number of the first cell and dragging it right two cells:
…A…B…C…
.|
1|…A$1+B$1…B$1+C$1…C$1+D$1
.|
Creating the calculations in a column and then using Paste Special… to transpose it to a row works, kinda. The formula has to be written without any $ and added to each cell individually before transposing them. While that will work, the calculations are pretty complicated, and that’s a lot of finding-and-replacing.
Thanks for the help.
(As an aside, one of the things that I really appreciate about this board is that people tend to answer the question asked as opposed to recommending a different question. I’ve been on too many boards where I’ll ask, for example, “What’s the best brand to buy if I want to paint my house green?” The answers I receive are most likely to be along the lines of, “You shouldn’t paint your house green, because it’ll lower the resale value,” or “If you convert to vinyl siding you won’t have to paint at all,” or “I live in an apartment and love it, because I never have to paint the exterior.” None of which, of course, are of any use. I’m glad no one has suggested that I reformat my data set and (correctly) assumes that I have a very valid reason for both tables being laid out the way they are.)
Here’s another possible solution, although it’s a bit messy and requires two extra rows. Put the cell references into two rows as plain text (that is, without the ‘=’), and refer to them using the INDIRECT function.
Something like this (assuming that the data is on Sheet1):
A B
|
1| Sheet1!A1 Sheet1!A2
2| Sheet1!B1 Sheet1!B2
3| =INDIRECT(A1)+INDIRECT(A2) =INDIRECT(B1)+INDIRECT(B2)
Then drag-fill all three rows. Excel will simply increment the number at the end of those text cells.
Of course, that only works if the first row of the input corresponds to the first column of the output. If that’s not the case, you’ll need to add or subtract an offset. Something like this:
Wow! Mbossa, I appreciate the time and thought you put into your answer, but I’m rather inexperienced with Excel and can’t follow what the variables are or how the formula performs it’s function. Unfortunately, without comprehending it, I can’t plug in the correct value for my purposes.
I’d assumed that there was a simple built-in function that would perform the action I needed, but I suspect that I’ll be doing a lot of manual labor.
Thanks for your response. I’ll continue playing with your formula to see if I can better understand it.
It might be easier to understand if I split the formula up a bit further. This time I put the data on the same sheet so it’s a bit easier to see what’s going on.
Drag-fill D1 and D2 to the right. Excel will automatically continue the series, and you’ll end up with the names of the cells you need (A1, A2, A3,…), but not their values.
Now drag-fill D4 and D5 to the right. Excel will update those formula references in the usual way, and you’ll end up with a transposed version of the input. Then you can use this transposed input in your super secret formula (D7).
The INDIRECT function applies an extra layer of indirection. =D1 means “find the cell named D1, take its value, and plonk it in here”, but =INDIRECT(D1) means “find the cell named D1, take its value, then find the cell whose name is that value, and plonk its value in here.”
I probably should have just uploaded the Excel sheet somewhere and posted the link, instead of translating it to ASCII art. But that would have been far too sensible.