I tried using the Excel help function, but I couldn't find what I was looking for, so I decided to turn to the genius of CF.net. I have a column of 400 cells that I need to subtract a correction factor from. In other words, every cell in that column needs to be subtracted from a single value. Anyone know how do to this? I'm sure it' really easy, I'm just having trouble figuring it out.
Go to the next column and start beside the cell with the first entry. Sometimes, you have to use a dollar sign if you are making a cell absolute. However, since you are just using a value and it sounds like the same number, you can just put that number in your formula. Example: let's say your data is all in column "C" and the information runs from cells C1 to C400. Go to cell D1 and input "=C1-VALUE". Then you can either copy that cell and paste the formula for the rest of the range of D2 to D400 or you can do the old drag and drop method. I hope this is what you are asking.
So if I have this right, you have a column of numbers like so: 12 14 15 22 ANd you need to subtract from a single number such as 100. So row 1 (12) would be 100-12. Row 2 (14) would be 100-14, and so on. In that case the formula (next to the first value) would be =sum(X-Cell #) So if the number you are subtracting from is 100 and the first value is cell A1, it would be =sum(100-A1) That should give you the value. Then just drag down with the little corner thingy on that cell with the forumla and it should calculate for each cell all the way down.
Yea, I couldn't tell if you needed to do a summation at the end, but if you do, Supermac's post is spot on. Another thing you could do is place your value in a cell, example input "100" in cell A1. And then you can do a formula but there you would have to put dollar signs around it to make it absolute, e.g. "$A$1". Some people like seeing that instead of hard coded numbers.
Alright, so I have a new question. I have about 4 worksheets with tons of calculated numbers (ie h2-$f$1 etc), but now I need to make a new worksheet with quite a few of these calculated numbers, in order to do more calculations. The problem is, Excel won't let me cut and paste the calculated values to the new worksheet and gives me an REF! error. I know why I'm getting the error, but is there anyway to get around it?
I'm assuming that your worksheets are in the same workbook, but you can insert a new worksheet. Then instead of cutting and pasting, you can do a couple of things: Copy instead of cut the values and paste special values on your new sheet or Have formulas that reference the cells in the other worksheet; example being that you are on the new sheet, cell A1. Type "=Sheet1!A5 (or whatever the cell reference is that has your value) + whatever (or minus or whatever operation you have to use).
Will you be manipulating the correction factor at all? If your column of values were in Column B, you could put your correction factor in A1. Then in cell C1, type the following formula: = $B1 - $A$1. Copy that all the way down.
I have been coping and pasting the numbers from worksheets in the same workbook, but getting the REF! error. I'll try your second idea though.
Manny, I typed "=pH=4.8!F1" in order to move cell F1 from sheet "pH=4.8" to my new worksheet, but I got the same REF! error. EDIT: It worked after I changed the name of the sheet. Thanks Manny!
you can also just go to the cell you want the value in, type "=", and then go click on the old cell you're trying to put in the workbook. easier than typing stuff out. oh, and if you have a formula that will apply to a column of numbers, once you type in the formula for one cell, instead of dragging it down, you can just double click on the spot where you would normally start dragging (bottom right corner), and it will auto fill in the whole column. this only works if the column is connected to the rest of the data (no blank columns in between), but is nice if you don't wanna scroll down a long way.
