Wednesday 24 October, 2007

Call yourself a dev.?

Filed under: Numbers and stuff, All things Excel

I have a colleague. For the sake of argument, let's call him Neil. (His real name is Neil.) He sent me an email today asking why the following Excel formula wasn't working.

=IF(F648=1, 10, IF(F648=2, 8, IF(F648=3, 7, IF(F648=4, 6, IF(F648=5, 5, IF(F648=6, 4, IF(F648=7, 3, IF(F648=8, 2, IF(F648=9, 1, 0)))))))))

[Note: I've added spaces after each comma to allow the for inevitable word-wrapping issues.]

My immediate response was that Excel can only cope with eight nested ifs. (The last argument in his email was red, and this was indeed the straw that was breaking the proverbial camel's back.)

I then asked what business problem he was trying to solve. He had a column of data containing values between 0 and 10. And he wanted to invert them, so that 0 became 10, 1 became 9, 5 stayed 5, 8 became 2 etc.

I suggested he instead used the following formula:

=10-F648.

There was a short pause on the other end of the line. Bless.


Posted by dan at 4:15am | Permalink | Comments (6) | Trackbacks (0)
Comments

i think what you mean is that 0 became 10. Else it doesn't make sense.

Ah. Bless. :)

Posted by Mark / Chris, 6:05am, Wednesday 24 October 2007

Corrected and suitably embarrassed. Neil should be way more so, though...

Posted by Dan 6:09am, Wednesday 24 October 2007

Typical Dan, not telling the full story... Dan forgot to use the vital word, test... The 'if' was a test... What I actually needed to do was convert the numbers to text... Bless indeed...

Posted by Neil, 6:30am, Wednesday 24 October 2007

Oh, you mean =TEXT(F648)

Posted by Dan 9:09am, Wednesday 24 October 2007

I have a calculator with 33 buttons (thirty-three!) Although I have no idea what ten of them are for.

Posted by Steve 7:12pm, Wednesday 24 October 2007

does 10-F648 work?
taking the first element of his original "IF(..." Neil wants the answer 10 when F648 is 1.

10 - 1 = 9.

11=F648 would work though.

11 - 1 = 10. QED.

Posted by JohnnyBall, 10:20pm, Tuesday 6 November 2007
Add comment (all comments are currently moderated)
Your name*
Enter the confirmation code* authimage
Comments *



* denotes a mandatory field