×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Problem with how to choose only certain data
2

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

RE: Problem with how to choose only certain data

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
www.mecaconsulting.com

RE: Problem with how to choose only certain data

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources