SERVING THE QUANTITATIVE FINANCE COMMUNITY

• 1
• 3
• 4
• 5
• 6
• 7
• 8

xpatagon
Posts: 54
Joined: June 1st, 2011, 1:31 pm

### Excel tricks

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

tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

A bizarre thing. Turning a cell containing =TRUE into %age returns 0.01(of course, =VRAI is the equivalent in French of =TRUE)

bearish
Posts: 5804
Joined: February 3rd, 2011, 2:19 pm

### Excel tricks

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.

tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

Maybe the bizarre thing is this casting to double!!?? Anyway ...

bearish
Posts: 5804
Joined: February 3rd, 2011, 2:19 pm

### Excel tricks

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".

tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

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 tagoma on April 23rd, 2013, 10:00 pm, edited 1 time in total.

Cuchulainn
Posts: 62932
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

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.
Step over the gap, not into it. Watch the space between platform and train.
http://www.datasimfinancial.com
http://www.datasim.nl

tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

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 tagoma on April 23rd, 2013, 10:00 pm, edited 1 time in total.

tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

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

Cuchulainn
Posts: 62932
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

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???????
Step over the gap, not into it. Watch the space between platform and train.
http://www.datasimfinancial.com
http://www.datasim.nl

tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

QuoteYEEPEE???????i meant 'yipee' .did i sound over enthusiastic?i like very much this free online tool

Cuchulainn
Posts: 62932
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

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??
Step over the gap, not into it. Watch the space between platform and train.
http://www.datasimfinancial.com
http://www.datasim.nl

tagoma
Posts: 18379
Joined: February 21st, 2010, 12:58 pm

### Excel tricks

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 tagoma on April 23rd, 2013, 10:00 pm, edited 1 time in total.

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...

 JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...

GZIP: On