Problem with how to choose only certain data
Problem with how to choose only certain data
(OP)
I am making a spreadsheet for share price data. I have a template that will accept data for the next three months. I need a way to tell excel to use the latest data entered in a certain column to compare with the previous data entered before it and the first data in the line.
For example:
IN THE SECOND WEEK
WEEK1 10
WEEK2 12
WEEK3
WEEK4
WEEK5
WEEK6
in this case i want the spreadsheet to use the week 2 data and week 1 data
IN THE 5TH WEEK
WEEK1 10
WEEK2 12
WEEK3 13
WEEK4 16
WEEK5 15
WEEK6 18
In this case i want to use the week 5 and 6 data to work out the change in share proce over the previous week and last and first data for overal gain loss.
I would like a formula to specify:
- the last number entered in the column
and - the number before the last number entered in the column. I can use the cell reference to specify the original share value
many thanks for your help
barny
For example:
IN THE SECOND WEEK
WEEK1 10
WEEK2 12
WEEK3
WEEK4
WEEK5
WEEK6
in this case i want the spreadsheet to use the week 2 data and week 1 data
IN THE 5TH WEEK
WEEK1 10
WEEK2 12
WEEK3 13
WEEK4 16
WEEK5 15
WEEK6 18
In this case i want to use the week 5 and 6 data to work out the change in share proce over the previous week and last and first data for overal gain loss.
I would like a formula to specify:
- the last number entered in the column
and - the number before the last number entered in the column. I can use the cell reference to specify the original share value
many thanks for your help
barny





RE: Problem with how to choose only certain data
I am sure there are many solutions, but here is how I would do it:
Col # | Row A Row B Row C
____________________________________
1 | Week # Price Index
2 | 1 10 if(B2>0,A2,0)
3 | 2 12 if(B3>0,A3,0)
4 | 3 13 if(B4>0,A4,0)
5 | 4 16 if(B5>0,A5,0)
6 | 5 15 if(B6>0,A6,0)
7 | 6 18 if(B7>0,A7,0)
MaxIndex = @Max(C2:C7)
LastVal = @index(B2:B7,MaxIndex)
NexLastVal = @index(B2:B7,MaxIndex-1)
Hope this helps.
Meca
www.mecaconsulting.com
RE: Problem with how to choose only certain data
* recent change in price: (week2-week1)
* overall change: (week2-week1)
in the second case you want to calculate
* recent change in price: (week6-week5)
* overall change: (week6-week1)
If I'm right about your goal, then you could achieve it as follows:
Col # | Row A Row B
__________________________
1 | Week # Price
2 | 1 10
3 | 2 12
4 | 3 13
5 | 4 16
6 | 5 15
7 | 6 18
8 | 7 *blank*
9 | 8 *blank*
...
65535 | 65534 *blank*
* recent change in price =index(B2:B65535,count(B2:B65535),1) - index(B2:B65535,count(B2:B65535)-1,1)
* overall change =index(B2:B65535,count(B2:B65535),1) - $B$2
recent change should equal (18)-(15) = 3
overall change should equal (18)-10 = 8