+ Reply to Thread
Results 1 to 14 of 14

Subtract value in a column when a certain range in crossed in another column

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    43

    Subtract value in a column when a certain range in crossed in another column

    I have two columns, Col A. is price and Col B. is time. I want to enter a formula in Col C to calculate price change, when time in Col B crosses 60 seconds (or 1 min.)

    Attached excel has values and brief details, which might help understanding what I mean.

    Looking forward to some quick replies. Would prefer a formula as i have no knowledge of VBA or Macro.

    Thank you very much, in advance.

    Please refer to attachment, which has the above table better displayed.
    Attached Files Attached Files
    Last edited by DexterG; 12-10-2012 at 06:04 AM. Reason: Sorry, mistake in question...new formula wanted!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Subtract value in a column when a certain range in crossed in another column

    In F3 copied down

    =IFERROR(INDEX($B$3:$B$18,IF(ISNUMBER(MATCH(D3+"0:01",$D$3:$D$18,0)),MATCH(D3+"0:01",$D$3:$D$18),MATCH(D3+"0:01",$D$3:$D$18)+1)),"")

    Does that work for you?

    Edit: I see you are listed as using 2003 so can't use IFERROR,

    =IF(ISERROR(MATCH(D3+"0:01", $D$3:$D$18)),"", INDEX($B$3:$B$18,IF(ISNUMBER(MATCH(D3+"0:01",$D$3:$D$18,0)),MATCH(D3+"0:01",$D$3:$D$18),MATCH(D3+"0:01",$D$3:$D$18)+1)))
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Subtract value in a column when a certain range in crossed in another column

    Perhaps soemthing like the attached ? cross.xls

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Subtract value in a column when a certain range in crossed in another column

    Oops, that just gives you the value to subtract and the ISERROR doesn't work right ( I should test these things ) Try

    =B3-INDEX($B$3:$B$18,IF(ISNUMBER(MATCH(D3+"0:01",$D$3:$D$18,0)),MATCH(D3+"0:01",$D$3:$D$18),MATCH(D3+"0:01",$D$3:$D$18)+1))
    You'll get error messages when you get within 1 minute of the end of your data.
    Does that work for you?

  5. #5
    Registered User
    Join Date
    12-07-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Subtract value in a column when a certain range in crossed in another column

    No, it just returns a price from below, doesn't subtract the price at Time1 from price at Time next price 1 min. later I have attached a new excel. Can you please have a look? it'll give you a better idea of what I mean.

    Thanks.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Subtract value in a column when a certain range in crossed in another column

    This should get rid of those pesky errors


    =IF(MATCH(D3+"0:01",$D$3:$D$18)=ROWS($D$3:$D$18),"",(B3-INDEX($B$3:$B$18,IF(ISNUMBER(MATCH(D3+"0:01",$D$3:$D$18,0)),MATCH(D3+"0:01",$D$3:$D$18),MATCH(D3+"0:01",$D$3:$D$18)+1))))

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Subtract value in a column when a certain range in crossed in another column

    My formula in F3 copied down
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-07-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Subtract value in a column when a certain range in crossed in another column

    ChemistB, your second formula worked perfectly!! Thank you so much, my reply of 'not working' was for the first formula!

    Thanks once again for the prompt reply!!!

  9. #9
    Registered User
    Join Date
    12-07-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Subtract value in a column when a certain range in crossed in another column

    SORRY! is it possible to alter the formula?

    I just realised, what I really want is

    Subtract the price at Time1 from price at Time last price at or before 1 min (and not after).

    I tried inserting < in place of + "00:01" but didn't help

    Thanks in advance

  10. #10
    Registered User
    Join Date
    12-07-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Subtract value in a column when a certain range in crossed in another column

    SORRY! is it possible to alter the formula?

    I just realised, what I really want is

    Subtract the price at Time1 from price at Time last price at or before 1 min (and not after).

    I tried inserting < in place of +"00:01" but didn't help

    Thanks in advance

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Subtract value in a column when a certain range in crossed in another column

    Yes, that simplifies things actually

    =B3-INDEX($B$3:$B$18,MATCH(C3+"0:01",$C$3:$C$18))
    Does that work for you? If not, what are you expecting?

  12. #12
    Registered User
    Join Date
    12-07-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Subtract value in a column when a certain range in crossed in another column

    PERFECT!!!!! Was expecting results which your formula provides!!

    if you don't mind, can you tell me why the formula doesn't need <0:01, or something that says that max. price should be 1min or lesser?

    I tried MATCH but kept getting errors as i would put <0:01.. a brief explanation of your formula would be great and help me understand and remember it!!

    Thanks a lot!!

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Subtract value in a column when a certain range in crossed in another column

    When you search with Match (unless you include the third argument of 0 which then looks for exact match) it searches for the largest value without going over. In the original equation, we compensated for that with the MATCH(D3+"0:01",$D$3:$D$18)+1.
    In the original equation, if it was an exact match (exactly 1 minute later) then you didn't want "+1" so we had to add in that whole internal IF statement which checked for an exact match (the MATCH formula had a 0 in it)
    IF(ISNUMBER(MATCH(D3+"0:01",$D$3:$D$18,0)),MATCH(D3+"0:01",$D$3:$D$18),MATCH(D3+"0:01",$D$3:$D$18)+1))
    Hope that helps.

  14. #14
    Registered User
    Join Date
    12-07-2012
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Subtract value in a column when a certain range in crossed in another column

    Understood & noted!! thanks a ton...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1