Oct 20 2008

A terrifying wall of IF statements

Published by Dougal at 7:18 pm under Computing, Friends, Programming

excel wall
excel wall
©

On Friday night Emily mentioned something interesting from her exciting adventures in Excel programming — you can only nest up to seven IF statements in a cell for conditional processing of data. This seems an annoying arbitrary limit, but then again, seven IF statements should be enough for anyone.

I can’t think of any circumstances where I would use seven nested statements, but I guess if you’re fitting it all in a spreadsheet cell you have a limited set of ways to approach the problem.

It seems there are a lot of other people out there who hit the 7-statement limit and ask for help. Actual help seems thin on the ground. One commenter suggests CHOOSE:

CHOOSE(position, result1, result2, ..., result29)

I don’t know if this would have solved Emily’s problem or not. She wanted something for a 10-value range but that’s all I can remember. So I’ll just invent a scenario that seems plausible. Cells A1 to A20 contain scores out of 5. The maximum value for the whole lot is 100. If we take this sum and divide it by 10, we divide the whole range of scores into ten-point increments, numbered 0 to 9. We add one and use this value to look up the respective value in the list to the right. 1 gives the first result, 2, the second and so on.

=CHOOSE((SUM(A1:A20)/10)+1, "nothing", "tiny", "small",
                            "ish", "nearly", "halfway",
                            "just over", "decent", "v.good",
                            "excellent", "awesome")

I don’t know if there are more subtle or flexible ways of approaching this problem that (a) don’t have arbitrary limitations and (b) don’t require dropping into VBA. Answers on a postcard.

8 responses so far

8 Responses to “A terrifying wall of IF statements”

  1. # Lawrenceon 20 Oct 2008 at 7:57 pm

    That this kind of thing even exists makes me somewhat depressed.

  2. # Emilyon 20 Oct 2008 at 8:01 pm

    Dammit, I was just about to go onto Facebook to chastise you for not having written a blog after me and Helen have and then you not only go and write one but you attempted to solve my problem. Men! They’re just so….bloody……considerate.

  3. # Robert Hulmeon 20 Oct 2008 at 8:53 pm

    IF gender == ‘male’ …

  4. # Robert Hulmeon 20 Oct 2008 at 8:54 pm
    1. Implement Turing machine in Excel
    2. ????
    3. Profit!!1
  5. # Robert Hulmeon 20 Oct 2008 at 8:55 pm

    Hmm your markup thing ate my 1 2 3

  6. # Dougalon 20 Oct 2008 at 9:45 pm

    @Rob: It did, and yet if you check the source, it apparently didn’t. I am sorely baffled.

  7. # Robert Hulmeon 20 Oct 2008 at 9:46 pm

    Try looking for the missing text in A5?

  8. # Dougalon 21 Oct 2008 at 6:28 am

    I came to the conclusion, while thinking the matter over, that this theme was put together by an idiot. The lack of numbered points in your <ol> is down to the style sheet using <li> elements to contain comments (really!) and not accounting for the fact that commenters might want to use that standard HTML feature too. So your list looks like a series of three comments separated by thin lines.

    Like I said, whoever put the theme together didn’t think that commenters would want lists and to therefore set up nested <li> styles with default/expected behaviour. I’ve been thinking for a while I need to abandon this theme. Maybe getting one that doesn’t surprise so often would be good?

Trackback URI | Comments RSS

Leave a Reply