Serving the Quantitative Finance Community

  • 1
  • 3
  • 4
  • 5
  • 6
  • 7
  • 9
 
User avatar
xpatagon
Posts: 0
Joined: June 1st, 2011, 1:31 pm

Excel tricks

January 24th, 2013, 7:22 pm

A an alternative method for Edouards date problem using IF and MIN would be C1 : =YEAR(MIN($A$1:$A$20000))C2 : =YEAR(MIN(IF(YEAR($A$1:$A$20000)>C1;$A$1:$A$20000;99999999)))entered as a 1 cell array formula, then just copy/fill down as required.Alternatively, if you dont want array formulas and dont mind the years in descending order thenC1 : =YEAR(MAX(A:A))C2 : =YEAR(VLOOKUP(DATEVALUE("12-31-"&(C1-1));A:A;TRUE))works nicely
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

March 1st, 2013, 9:26 pm

A bizarre thing. Turning a cell containing =TRUE into %age returns 0.01(of course, =VRAI is the equivalent in French of =TRUE)
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

March 1st, 2013, 11:31 pm

It's not that bizarre. In Excel (as opposed to say, VBA for Excel), TRUE evaluates to 1.0 when implicitly cast to a double. What you are doing is making it 1.0 and then dividing by 100 by appending the % sign. If the cell had been formatted as % it would show 1%, which would look more natural, I guess.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

March 2nd, 2013, 9:36 am

Maybe the bizarre thing is this casting to double!!?? Anyway ...
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Excel tricks

March 2nd, 2013, 1:56 pm

If you think that is bizarre, how about this one? =((7&7)/"77"&1)*1.01Naturally, it evaluates to 11.11.Excel is extremely accommodating when it comes to casting from one type to another as needed for operations to "make sense".
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

April 24th, 2013, 10:27 am

I'm sorry, this is basic, and you're probably going to laugh, but this is something that puzzles me.I'm expecting ln(x/y)=ln(x)−ln(y) [something like the (natural) logarithm quotient rule, see Laws of Logarithms]But when doing some trials with Excel, I can't get such an equality. There is always a slight difference between ln(x/y) and ln(x)−ln(y).??Example of results in Excel:ln(3200/2345) = 0.310865407907438000000ln(3200) - ln(2345) = 0.310865407907439000000
Last edited by tags on April 23rd, 2013, 10:00 pm, edited 1 time in total.
 
User avatar
Cuchulainn
Posts: 20254
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

April 24th, 2013, 10:30 am

These days nothing surprises me anymore.See the Math Accuracy thread. Floating point number do not (necessarily) obey they rules of arithmetic. It's called rounding errors.
Last edited by Cuchulainn on April 23rd, 2013, 10:00 pm, edited 1 time in total.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

April 24th, 2013, 10:31 am

QuoteOriginally posted by: edouardI'm sorry, this is basic, and you're probably going to laugh, but this is something that puzzles me.I'm expecting ln(x/y)=ln(x)−ln(y) [something like the (natural) logarithm quotient rule, see Laws of Logarithms]But when doing some trials with Excel, I can't get such an equality. There is always a slight difference between ln(x/y) and ln(x)−ln(y).??Example of results in Excel:ln(3200/2345) = 0.310865407907438000000ln(3200) - ln(2345) = 0.310865407907439000000Ouch! And, I also get such unexpect results with R> options(digits = 22)> log(3200/2345)[1] 0.3108654079074381737513> log(3200) - log(2345)[1] 0.3108654079074391063386> log(3200/2345) == log(3200) - log(2345)[1] FALSE
Last edited by tags on April 23rd, 2013, 10:00 pm, edited 1 time in total.
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

April 24th, 2013, 10:49 am

QuoteOriginally posted by: CuchulainnThese days nothing surprises me anymore.See the Math Accuracy thread. Floating point number do not (necessarily) obey they rules of arithmetic. It's called rounding errors.By curiosity, I ran the same example with the online engine Wolfram Alpha. That works. Yeepee!ln(3200/2345) = log(3200) - log(2345) ???? TRUE
 
User avatar
Cuchulainn
Posts: 20254
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

April 24th, 2013, 11:25 am

QuoteOriginally posted by: edouardQuoteOriginally posted by: CuchulainnThese days nothing surprises me anymore.See the Math Accuracy thread. Floating point number do not (necessarily) obey they rules of arithmetic. It's called rounding errors.By curiosity, I ran the same example with the online engine Wolfram Alpha. That works. Yeepee!ln(3200/2345) = log(3200) - log(2345) ???? TRUEYEEPEE???????
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

April 24th, 2013, 11:37 am

QuoteYEEPEE???????i meant 'yipee' .did i sound over enthusiastic?i like very much this free online tool
 
User avatar
Cuchulainn
Posts: 20254
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

April 24th, 2013, 11:48 am

QuoteOriginally posted by: edouardQuoteYEEPEE???????i meant 'yipee' .did i sound over enthusiastic?i like very much this free online tool It works for Wolfram but not for Excel. yipee??
 
User avatar
tags
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Excel tricks

April 24th, 2013, 4:10 pm

QuoteOriginally posted by: Cuchulainn It works for Wolfram but not for Excel. yipee??No, no this is not what I meant. I think that's sad Excel shows such limitations. I don't fell that what I asked him to compute is ridiculously complex.At the same time, I'm glad math wolfram alpha's result TRUE confirmed that I have not turned mentally insane (and it is a cool online tool))Do you know how Matlab would behave in such circumstances?
Last edited by tags on April 23rd, 2013, 10:00 pm, edited 1 time in total.