Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calculating Difference Between 2 Columns (2000)

    I have the attached file where when I update daily prices, the last highlighted column give ther most recent price. What I would like to do is in the adjacent column show the change between the most recent trade price, and the previous day's price.
    Any ideas?
    Thanks.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Difference Between 2 Columns (2000)

    Jeff,

    See the attached workbook. I have used some intermediate results which I have left visible by intent, so that you can see what happens. You can hide these results of course.

    Note: the calculation for "previous day" may return unexpected results if there is only 1 column with prices.

    Regards,
    Hans
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculating Difference Between 2 Columns (2000)

    Hans,
    Looks like you have done the trick again! Thanks!
    Would you mind explaining what the formulas do in the AM column?
    Thanks,
    Jeff

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Difference Between 2 Columns (2000)

    The formula in AM1 calculates the last day with a price. In the workbook you attached, this was part of the formulas in column AH, but since it was repeated there, I took it out. It uses the COMPARE with an extremely large number trick to find the last numeric entry.
    The formula in AM2 uses the OFFSET function to get the range from cell B4 to the cell to the left of the one with the most recent price, and then uses the same trick to find the last numeric entry in that range (I used a different extremely large number, but that is not significant).

    AM1 is used in the formulas in column AH, and AM2 in the formulas in column AK. Column AI is the difference between them.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Calculating Difference Between 2 Columns (2000)

    FWIW, attached has different formulas in cells AM1 and AM2. It looks like the entered values are always increasing throughout the month, but if they didn't, I don't think your formula would work correctly(?). My formula in AM1 is an array formula returning the latest day for which a number has been entered.

    The formula in AM2 is a similar array formula, but its looking in a smaller range (to the left of the day returned in AM1). The formula should actually handle 'long weekends' should they ever arise, i.e., situations where there might be 3 or more days where prices would be N/A. As with your version, I'm not sure what happens if only one day of prices have been entered.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Calculating Difference Between 2 Columns (2000)

    Oops. Ignore my last post. I think I'm wrong on both counts. Your formula in AM1 works fine even if the numbers aren't ascending. Plus, your AM2 formula also handles long weekends. My fomulas still work (I think!), but yours are better since they aren't array formulas.
    Oh well.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Difference Between 2 Columns (2000)

    There is no need for choosing a different big number whenever you need it in a MATCH formula.

    Just enter in some cell in a worksheet you can name Admin:

    9.99999999999999E+307

    Select this cell, go to the Name Box on the Formula Bar, type BigNum, hit enter.

    Or, activate Insert|Name|Define, enter BigNum as name in the Names in Workbook box, enter 9.99999999999999E+307 in the Refers to box, and click OK.

    Now you can simply use:

    =MATCH(BigNum,B4:AF4) in AM1

    =MATCH(BigNum,B4:OFFSET(B4,0,MAX(AM1-2,0))) in AM2.

    Jeff appears not to register any prices in weekend days. He could delete these weekend columns from his worksheet.
    If they are removed, then the formula in AM2 must be a different one:

    =MATCH(WORKDAY(AH2,-1),B2:AF2)

    Aladin
    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Difference Between 2 Columns (2000)

    Hi Aladin,

    Your suggestion of using a named constant is excellent.

    There was no particular reason for using two different big numbers in the formulas. Jeff already had a MATCH formula and I slapped another one together without paying attention to what he had used. I noticed it afterwards, but didn't bother changing the spreadsheet. Using a named constant makes the formulas more readable.

    Regards,
    Hans

Posting Permissions

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