Author |
Topic: Microsoft Excel Questions (Read 7020 times) |
|
SWF
Uberpuzzler
Posts: 879
|
 |
Microsoft Excel Questions
« on: Aug 12th, 2005, 7:47pm » |
 Quote  Modify
|
Anyone who wants to work for Microsoft should be able demonstrate a basic understanding of the workings of Excel. So here are some questions, that are meant to be answered during an interview (without the help of a computer).
Suppose cell A1 contains the formula "=(1-1/3)/(2/3)". For each of the following formulas that can be placed in another cell, describe what the formula might be used for, and what the resulting value will be:
Formula 1: = IF ( ABS(A1)<=1, ACOS(A1), "Complex Number")
Formula 2: = ( (1/A1) - INT(1/A1) ) < 0
Formula 3: = (A1 = 1)
|
|
 IP Logged |
|
|
|
Botox
Guest
|
 |
Re: Microsoft Excel Questions
« Reply #1 on: Aug 27th, 2005, 8:25am » |
Quote Modify
 Remove
|
This is simple if you understand Excel logic.
1) By checking if the value is <=1 before using arccosine, this statement never gives an error. For this case, A1 is 1, it uses the first part, acos(a1), and will return 0.
2) x minus int(x) is never negative. The result of this formula is TRUE.
3) A1 does equal 1. The result of this is TRUE
|
|
IP Logged |
|
|
|
Botox
Guest
|
|
Re: Microsoft Excel Questions
« Reply #2 on: Aug 27th, 2005, 8:27am » |
Quote Modify
Remove
|
For part 2, I mean FALSE.
|
|
IP Logged |
|
|
|
BNC
Uberpuzzler
Gender: 
Posts: 1732
|
|
Re: Microsoft Excel Questions
« Reply #3 on: Aug 27th, 2005, 11:04pm » |
Quote Modify
|
Botox,
Now try it on the Excel.... 
|
|
IP Logged |
How about supercalifragilisticexpialidociouspuzzler [Towr, 2007]
|
|
|
Icarus
wu::riddles Moderator
Uberpuzzler
Boldly going where even angels fear to tread.
Gender:
Posts: 4863
|
|
Re: Microsoft Excel Questions
« Reply #4 on: Aug 29th, 2005, 4:52pm » |
Quote Modify
|
For the record, this is what I got using MS Excel 2000 9.0.3821 SR-1. The values indicated are those displayed for the cells containing the indicated formula.
[Formula for cell A1]: =(1-1/3)/(2/3) : value= 1.000000000000000000000000000000 (to 30 decimal places)
=ABS(A1) : value = 1
=ACOS(A1) : value = 0
=IF(ABS(A1)<=1, ACOS(A1), "Complex Number") : value = #NUM!
=( (1/A1) - INT(1/A1) ) < 0 : value = TRUE
=( (1/A1) - INT(1/A1) ) : value = -2.2E-16
= ( A1 = 1 ) : value = TRUE
|
|
IP Logged |
"Pi goes on and on and on ...
And e is just as cursed.
I wonder: Which is larger
When their digits are reversed? " - Anonymous
|
|
|
SWF
Uberpuzzler
Posts: 879
|
|
Re: Microsoft Excel Questions
« Reply #5 on: Aug 29th, 2005, 7:22pm » |
Quote Modify
|
Yes, Icarus that is what I get too. Does anybody find it odd that the arc cos expression gives a error after checking for >1, and that A1-int(A1) actually turns out to be negative? It sure surprised me, expecially since the 3rd expressions tells me that A1 does equal 1.
With the Linux Excel clone, there is the same roundoff error making it not equal to 1, but the logic statements work properly, thus making it incompatible with Excel.
The funny thing is that I ran across this while trying to use Excel, and was getting weird results which were tracked down to this issue.
|
|
IP Logged |
|
|
|
Grimbal
wu::riddles Moderator
Uberpuzzler
Gender:
Posts: 7527
|
|
Re: Microsoft Excel Questions
« Reply #6 on: Aug 30th, 2005, 1:25am » |
Quote Modify
|
I think Microsoft was trying to make Excel behave in a more "intelligent" way. Most people won't understand rounding error in floating points, so they made Excel add some rounding and be error tolerant "where necessary". If A1 is very close to 1, they will assume 1 is what was meant and will accept "A1=1" as true. Of course, A1<=1 will also be true even if A1 is slightly bigger.
But while they make it behave better in some situation, it backfires on other situations where it becomes worse.
What they have to explain me is the subtle difference between
=( (1/A6) - INT(1/A6) )
and
=(1/A6) - INT(1/A6)
|
|
IP Logged |
|
|
|
Icarus
wu::riddles Moderator
Uberpuzzler
Boldly going where even angels fear to tread.
Gender:
Posts: 4863
|
|
Re: Microsoft Excel Questions
« Reply #7 on: Aug 30th, 2005, 2:53pm » |
Quote Modify
|
The behavior I found most curious was that, Excel was able to calculate ACOS(A1) when it appeared by itself in a formula. But when the exact same expression appears within the IF function, Excel is no longer able to calculate its value.
on Aug 30th, 2005, 1:25am, Grimbal wrote:What they have to explain me is the subtle difference between
=( (1/A6) - INT(1/A6) )
and
=(1/A6) - INT(1/A6) |
|
I would like to understand that one too. All I can figure is that the parentheses cause the difference to undergo a datatype conversion at a different point than the unparenthesed version. This untimely conversion somehow causes the junk digits to be preserved when otherwise they would be discarded.
One final curiousity: When I set up the calculation the other day, Excel handled "=ACOS(A1)" as a formula, giving 0. But just now I set it up again to experiment with a couple things, and this time it doesn't work. "=ACOS(A1)" is giving me "#NUM!" even by itself.
Far behind the minds of mortal men are the works of mister Gates.
|
|
IP Logged |
"Pi goes on and on and on ...
And e is just as cursed.
I wonder: Which is larger
When their digits are reversed? " - Anonymous
|
|
|
SWF
Uberpuzzler
Posts: 879
|
|
Re: Microsoft Excel Questions
« Reply #8 on: Aug 30th, 2005, 6:39pm » |
Quote Modify
|
Another unusual thing is if in A2 you put =1-5e-16, then in another cell put variations of the formula
=(A2-x = 1-x) where x has various values
For x as 0, .1, .2, .4, .5, .6 I get TRUE, but when x is .3 the result is FALSE
|
|
IP Logged |
|
|
|
Icarus
wu::riddles Moderator
Uberpuzzler
Boldly going where even angels fear to tread.
Gender:
Posts: 4863
|
|
Re: Microsoft Excel Questions
« Reply #9 on: Aug 31st, 2005, 3:25pm » |
Quote Modify
|
Odd - I get just the opposite: TRUE for .3 and FALSE for the others. It is also TRUE for 1.4 and for everything >=2. Examining more closely reveals interesting patterns of what comes in true and what comes in false. From .282 to .312, incrementing by 0.001, it alternates between TRUE and FALSE (TRUE when the last digit is even). But incrementing by 0.0001 shows that the TRUEs show up in 3-wide islands centered around .xxx9. Incrementing by 0.00001 shows less regular behavior with TRUEs showing up at varying intervals.
All of this is to me a general example of why depending upon "=" relation for floating point values is not a good idea (generally - not just for MS products). When comparing floating points, you should always establish an interval for comparison (for instance: "ABS(x - y) < tol", rather than just "x == y").
|
|
IP Logged |
"Pi goes on and on and on ...
And e is just as cursed.
I wonder: Which is larger
When their digits are reversed? " - Anonymous
|
|
|
Grimbal
wu::riddles Moderator
Uberpuzzler
Gender:
Posts: 7527
|
|
Re: Microsoft Excel Questions
« Reply #10 on: Sep 1st, 2005, 6:20am » |
Quote Modify
|
It is not a problem with =, but with floating point arithmetic. 1/3 + 1/3 + 1/3 is not 1.0.
What they did was to break '=' to make believe addition works correctly. But they just moved the problem somewhere else. Unfortunately they also broke '<=' which is not as dangerous as "=" between floating point numbers.
|
|
IP Logged |
|
|
|
Icarus
wu::riddles Moderator
Uberpuzzler
Boldly going where even angels fear to tread.
Gender:
Posts: 4863
|
|
Re: Microsoft Excel Questions
« Reply #11 on: Sep 1st, 2005, 3:06pm » |
Quote Modify
|
I didn't say that the problem was in the = relation. But any time you are dealing with floating point numbers on computers, you have to expect a certain amount of error in your calculations. Because of that error, you shouldn't ever ask if two floating-point numbers are equal. Because if they were calculated by differing procedures, they probably will not be equal, even if they should be by the underlying mathematics.
Rather, you should check to see if their difference is acceptably small.
|
|
IP Logged |
"Pi goes on and on and on ...
And e is just as cursed.
I wonder: Which is larger
When their digits are reversed? " - Anonymous
|
|
|
|
RIDDLES SITE
WRITE MATH!
Home 
Help 
Search 
Members 
Login 
Register
Reply
Notify of replies
Send Topic
Print