Problem with how to choose only certain data 2

[barny]

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

 

[meca]

Barny,

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

http://www.mecaconsulting.com

 

[ivymike]

let me make sure I understand the question - in the first case, you want to calculate
* 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