Page 5 of 8

Excel tricks

Posted: January 24th, 2013, 7:22 pm
by xpatagon
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

Excel tricks

Posted: March 1st, 2013, 9:26 pm
by tagoma
A bizarre thing. Turning a cell containing =TRUE into %age returns 0.01(of course, =VRAI is the equivalent in French of =TRUE)

Excel tricks

Posted: March 1st, 2013, 11:31 pm
by bearish
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.

Excel tricks

Posted: March 2nd, 2013, 9:36 am
by tagoma
Maybe the bizarre thing is this casting to double!!?? Anyway ...

Excel tricks

Posted: March 2nd, 2013, 1:56 pm
by bearish
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".

Excel tricks

Posted: April 24th, 2013, 10:27 am
by tagoma
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

Excel tricks

Posted: April 24th, 2013, 10:30 am
by Cuchulainn
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.

Excel tricks

Posted: April 24th, 2013, 10:31 am
by tagoma
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

Excel tricks

Posted: April 24th, 2013, 10:49 am
by tagoma
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

Excel tricks

Posted: April 24th, 2013, 11:25 am
by Cuchulainn
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???????

Excel tricks

Posted: April 24th, 2013, 11:37 am
by tagoma
QuoteYEEPEE???????i meant 'yipee' .did i sound over enthusiastic?i like very much this free online tool

Excel tricks

Posted: April 24th, 2013, 11:48 am
by Cuchulainn
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??

Excel tricks

Posted: April 24th, 2013, 4:10 pm
by tagoma
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?