| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value in the last cell of a moving column
I am tracking certain items, a column for each, and the data is entered in
the current last data cell in that column. Each time I enter data I insert a row (there are statistics being run on the data below the last data entry). I would like to calculate the difference from the currently entered data from the first entry for that column. I would like to reference the newly entered data without having to enter the data twice. Example: A1 Day 1 Day 2 A2 100 100 A3 200 200 A4 500 500 A5 300 A6 Average 267 A7 Delta 400 Average 275 A8 Delta 200 On day 1 I want the difference between cell A2 and A4 and the average formula is =average(a2:a5). On day 2 I have inserted a row to move the statistics down and I now want the difference between cells A2 and A5 and the average formula is =average(a2:a6). Is there a way to make that reference? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value in the last cell of a moving column
Doesn't
=average(A$2:A5) do it? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "rtbrd" wrote in message ... I am tracking certain items, a column for each, and the data is entered in the current last data cell in that column. Each time I enter data I insert a row (there are statistics being run on the data below the last data entry). I would like to calculate the difference from the currently entered data from the first entry for that column. I would like to reference the newly entered data without having to enter the data twice. Example: A1 Day 1 Day 2 A2 100 100 A3 200 200 A4 500 500 A5 300 A6 Average 267 A7 Delta 400 Average 275 A8 Delta 200 On day 1 I want the difference between cell A2 and A4 and the average formula is =average(a2:a5). On day 2 I have inserted a row to move the statistics down and I now want the difference between cells A2 and A5 and the average formula is =average(a2:a6). Is there a way to make that reference? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value in the last cell of a moving column
Yes, for the average but I want to update the difference formula
automatically on a daily basis without making manual changes. Delta Day 1 = a4 - a2 and day 2 sould be = a5 - a2 but when I insert a row the delta calculation remains at the previous cell, for day 2 after insertion the formula remains = a4 - a2. I want a4 to change to a5 when I insert a row. "Bob Phillips" wrote: Doesn't =average(A$2:A5) do it? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "rtbrd" wrote in message ... I am tracking certain items, a column for each, and the data is entered in the current last data cell in that column. Each time I enter data I insert a row (there are statistics being run on the data below the last data entry). I would like to calculate the difference from the currently entered data from the first entry for that column. I would like to reference the newly entered data without having to enter the data twice. Example: A1 Day 1 Day 2 A2 100 100 A3 200 200 A4 500 500 A5 300 A6 Average 267 A7 Delta 400 Average 275 A8 Delta 200 On day 1 I want the difference between cell A2 and A4 and the average formula is =average(a2:a5). On day 2 I have inserted a row to move the statistics down and I now want the difference between cells A2 and A5 and the average formula is =average(a2:a6). Is there a way to make that reference? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value in the last cell of a moving column
How about using
=OFFSET(A5,-1,0)-$A$2 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "rtbrd" wrote in message ... Yes, for the average but I want to update the difference formula automatically on a daily basis without making manual changes. Delta Day 1 = a4 - a2 and day 2 sould be = a5 - a2 but when I insert a row the delta calculation remains at the previous cell, for day 2 after insertion the formula remains = a4 - a2. I want a4 to change to a5 when I insert a row. "Bob Phillips" wrote: Doesn't =average(A$2:A5) do it? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "rtbrd" wrote in message ... I am tracking certain items, a column for each, and the data is entered in the current last data cell in that column. Each time I enter data I insert a row (there are statistics being run on the data below the last data entry). I would like to calculate the difference from the currently entered data from the first entry for that column. I would like to reference the newly entered data without having to enter the data twice. Example: A1 Day 1 Day 2 A2 100 100 A3 200 200 A4 500 500 A5 300 A6 Average 267 A7 Delta 400 Average 275 A8 Delta 200 On day 1 I want the difference between cell A2 and A4 and the average formula is =average(a2:a5). On day 2 I have inserted a row to move the statistics down and I now want the difference between cells A2 and A5 and the average formula is =average(a2:a6). Is there a way to make that reference? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value in the last cell of a moving column
Thanks Bob that works.
"Bob Phillips" wrote: How about using =OFFSET(A5,-1,0)-$A$2 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "rtbrd" wrote in message ... Yes, for the average but I want to update the difference formula automatically on a daily basis without making manual changes. Delta Day 1 = a4 - a2 and day 2 sould be = a5 - a2 but when I insert a row the delta calculation remains at the previous cell, for day 2 after insertion the formula remains = a4 - a2. I want a4 to change to a5 when I insert a row. "Bob Phillips" wrote: Doesn't =average(A$2:A5) do it? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "rtbrd" wrote in message ... I am tracking certain items, a column for each, and the data is entered in the current last data cell in that column. Each time I enter data I insert a row (there are statistics being run on the data below the last data entry). I would like to calculate the difference from the currently entered data from the first entry for that column. I would like to reference the newly entered data without having to enter the data twice. Example: A1 Day 1 Day 2 A2 100 100 A3 200 200 A4 500 500 A5 300 A6 Average 267 A7 Delta 400 Average 275 A8 Delta 200 On day 1 I want the difference between cell A2 and A4 and the average formula is =average(a2:a5). On day 2 I have inserted a row to move the statistics down and I now want the difference between cells A2 and A5 and the average formula is =average(a2:a6). Is there a way to make that reference? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving scattered cell data to new column? | Excel Worksheet Functions | |||
| How do i keep a column from moving | Excel Discussion (Misc queries) | |||
| moving column/cell data to rows/cells fails | Excel Worksheet Functions | |||
| Why is my tab key moving my cursor from column A to column k? | Excel Discussion (Misc queries) | |||
| moving every other row up 1 row to the next column | New Users to Excel | |||
Linear Mode
