Results 1 to 6 of 6

Thread: Ignore empty cells in an IF formula

  1. #1

    Ignore empty cells in an IF formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All.
    I've attached a spreadsheet that I'm having a bit of a problem with. The formula in J2 awards 4 points if St. Monicas defeats another team & awards 2 points in the case of a draw. My problem is that if there are no scores entered in columns D or F it awards 2 points as blank cells are counted as equal. Is it possible for excel to ignore cells that are empty in an IF formula?
    Many thanks in advance
    Mop
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Hi
    Ive worked extensively with data analysis, and I often wished that there wasn't so much special formatting/merged cells etc, which makes it difficult to develop formulae that would otherwise be quite simple.
    In this case, I would add a helper column (G) to place H, D, or A according to the result, and change the formulae to SUMPRODUCT (*4) or (*2) to calculate the points. On the other hand, if you created two extra tables sorted (a) by home team and (b) by away team with no gaps you could include the points awarded and do it by simple addition.

    HTH

  3. #3
    Thanks Hercules. I'll fiddle around with your suggestions. Just thought there would be a simple addition to the current formula. The example spreadsheet was a very basic version of the original. Once again thanks for your help. Much appreciated.

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,467
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Quote Originally Posted by Hercules1946 View Post
    Hi
    I often wished that there wasn't so much special formatting/merged cells etc, which makes it difficult to develop formulae that would otherwise be quite simple.
    Second the motion

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Hi Mop
    Im assuming that the problem lies where your team table is built, but the scores aren't in, so that they are of equal value? I can't see a simple amendment to the IF statements because your processing an array of data, and you would need an extra IF to test for blanks within each +IF thats testing for equal is TRUE, so it will make it much longer. I still think that it might be easier if you added extra data as partr of your table to indicate Home, Away, or Draw instead of trying to do it with one formula, but without seeing the full spreadsheet, I don't know how much work would be required.
    If I can get a bit of spare time today I will try using the SUMPRODUCT approach with your sample.

    Will post further later

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Hi Mop
    Ive added an extra table to the example you posted (P1:S9) that does the calculations using the SUMPRODUCT function. If you set the scores to blank, it doesn't treat them as equal by adding 2.
    The points formula is a bit lengthy, but once its constructed it can be copied down. In my example, Ive restricted the arrays to rows 1 to 34, which covers your Div1a teams. If you set this to cover your longest list of fixtures, then you can also copy the formulae across sheets (provided that they are laid out the same).

    Hope that helps
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •