# Call yourself a dev.?

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, 9, 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.

6 Responses to “Call yourself a dev.?”

1. Mark / Chris on October 24th, 2007 06:05

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

Ah. Bless. 🙂

2. Dan on October 24th, 2007 06:09

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

3. Neil on October 24th, 2007 06:30

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…

4. Dan on October 24th, 2007 09:09

Oh, you mean =TEXT(F648)

5. Steve on October 24th, 2007 19:12

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

6. JohnnyBall on November 6th, 2007 20:20

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.