I use Excel to run a very simple gradebook for my classes. What I wish to know is this: is there a way to add a constant to every numerical value in a column?
Example:
Say for an assignment I have the grades listed in a column, let’s call it A as this:
A
1 82
2 55
3 77
4 74
5 81
7 18
I wish to apply a modifier of +18 (A7) to every value in that column. So A1 will be 100, A2 will be 73 and so on. Is this possible or am I doomed to run into a circular reference problem?
In column B, just put =A7+18. Copy that down all the way.
Oops, read that wrong, put =A1+$A$7 instead of what I said above.
Thanks for the reply. I tried that but this simply changes the value of A1 to 18.
I know how to apply a constant, referenced in a cell, to an entire column by making a new column for the adjusted number. I just want to skip the creation of the new column if at all possible.
A quick hijack. What do the $ signs do in the equation? How does =A1+$A$7 differ from =A1+A7?
I don’t believe there’s any way to do that, precisely because of the circular reference problem (as you correctly suspected). The best you can do is put the new values in another column, then copy that new column, and “paste values” back into column A (a regular paste gets you into trouble, so you need a “paste… special.”)
Say you’ve put this formula into B1: “=A1+A7”. Now, you copy that formula into cells B2 through B5. The formula in B2 will be “=A2+A8”, the formula in B3 will be “=A3+A9”, and so on. The cell references are “relative,” so they change as you move down.
Now, say you’ve put this formula into B1: “=A1+$A$7”. Now, you copy that formula into cells B2 through B5. The formula in B2 will be “=A2+$A$7”, the formula in B3 will be “=A3+$A$7”, and so on. The “$A$7” reference is “absolute,” and doesn’t change when it’s copied elsewhere.
Does that help?
Usually, if you copy a formula from one cell to another, the cell references are changed. So if you have =A1+A7 in cell B1, and you copy it to cell C8, the formula becomes =B8+B14 . Putting a dollar sign before the column letter or line number fixes the reference, so if you copy =A1+$A$7 from cell B1 to cell C8, the formula becomes =B8+$A$7 .
It’s pretty simple to raise a number of cells by the same amount. Find a cell with the modifier value (18, in your case) Copy that cell. Now highlight the range of cells you wish to modify. Don’t hit paste. I repeat, don’t hit paste. Either right click or go under the ‘edit’ drop down and choose paste special. Leave the paste method (it probably says ‘all’) and under the operation section, choose add. This will add the cell you copied to the range you selected.
Keep in mind, this will replace the original values with the new calculated values. A1 will become 100 and the 82 will be lost. If you need to keep the original value you’ll need to create a new column with a formula, as interface2x suggested.
You can get around creating a new column in some circumstances. Depends exactly what you’re doing: For example, if you wanted to SUM your cells, each of which wants 18 added to it, then you could do:
=SUM(A1:A5)+A7*COUNT(A1:A5)
Rather than making a new column and SUMing that.
But you can’t do stuff like include the formula =A1+5 in cell A1, as you’ve discovered.
Some good help and suggestions all around. And I learned some new things. Thanks, Dopers!
Yes it does, thank you.