Spreadsheet newb used to databse functions here. I'm working with CSV files and want to replace values in a column's cells, row by row, based on logical tests of values of other cells in the same row. Is there some way I can use a formula to define the values of every cell in a column in terms of other cells in the same row?
Got a tip for us?
Let us know
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
automatically calculating cells of entire column from other columns in numbers
- Thread starter macmesser
- Start date
- Sort by reaction score
Look into the IF function. It can use values in other cells to calculate or replace values in the current cell.
Thanks. I got a formula to work on one cell. Is there some way to reference entire columns in a calculation so that all values of another column are calculated?
Maybe this may help. I don’t know your specifics so my answer is vague.
superuser.com
Google (or Bing) is your friend.
Reference a range of cells on all sheets in Excel
In Excel, =$J:$J references an entire column on the current sheet while ='Sheet2'!$J:$J references that entire column on Sheet2 explicitly. Is there a way to reference column J on all sheets without
Google (or Bing) is your friend.
Thanks.Maybe this may help. I don’t know your specifics so my answer is vague.
Reference a range of cells on all sheets in Excel
In Excel, =$J:$J references an entire column on the current sheet while ='Sheet2'!$J:$J references that entire column on Sheet2 explicitly. Is there a way to reference column J on all sheets without
Google (or Bing) is your friend.
Are you asking if there's an easy way to repeat what you did for one row on all the other rows? If that's your question, you can copy the cell you set up with the formula. Then select all the other cells that you want to have the same formula, and paste.
The pasted cells will have the cells they reference adjusted based on offset from the cell you copied (unless you did something that is unlikely and not worth mentioning).
Thanks! As is obvious, I am pretty unfamiliar with spreadsheets. I think this is just what I want to do. If I'm understanding correctly, the cell offset in the original formula becomes the column offset and all cells in the offset column are calculated accordingly because the logical and numerical relationships between cells as defined in the formula, would be repeated in each row.
I think you are exactly right. On the chance I'm misunderstanding you, I'll say it in my own words.
If I'm typing a formula in a cell, I might type the coordinates of a referenced cell like "C2" (column "C", row 2). But, it doesn't get recorded like that internally. If you're typing that into a call at "E7", it gets recorded as offsets: -2 horizontal and -5 vertical. So, if you copy and paste the E7 cell, the offsets are copied. So final destination of the paste will not reference "C2", but some other cell. So, if you paste into cell "F20", the referenced cell would be "D15".
original E7 references C2
pasted F20 references D15
You can change the C2 reference to be absolute in either or both dimensions. If you are editing the formula in cell E7, you'll see a down-arrow on C2. In the popup that it gives you, you can select "Preserve Row" and "Preserve Column". That means "Preserve when copying". For example, if you only select "Preserve Row", then:
original E7 references C$2 (The $ is used to indicate an absolute reference in the vertical dimension.)
pasted F20 references D$2