Results 1 to 8 of 8

20020816, 14:32 #1
 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.

20020816, 15:31 #2
 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

20020816, 17:04 #3
 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

20020816, 17:13 #4
 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.

20020816, 17:52 #5
 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.

20020816, 17:59 #6
 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.

20020817, 07:33 #7
 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 InsertNameDefine, 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(AM12,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)
AladinMicrosoft MVP  Excel

20020819, 08:23 #8
 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