Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Make Formula ignore blank cells

This is a discussion on Make Formula ignore blank cells within the Excel Questions forums, part of the Question Forums category; Hi all, need help regarding how to make a formula ignore blank cells. I have formulas within a worksheet that ...

  1. #1
    New Member
    Join Date
    Aug 2004
    Posts
    22

    Default Make Formula ignore blank cells

    Hi all, need help regarding how to make a formula ignore blank cells. I have formulas within a worksheet that looks at the values contained within a row of cells and then compares them with the values in the next row. There are 3 formulas, the first looks to see if the values in the upper row are larger, the second looks for larger values in the lower row and the third looks for matching values. The following shows how i am employing these in my worksheet, basically i'm analysing football results and the formulas give me a breakdown of games won, lost and drawn. The trouble i'm having is that the third formula that looks for matches is comparing blank cells with blank cells and so is distorting my results. How do i get it to ignore blank cells. I've searched through the message boards but after reading through them i'm not sure whether to use Isblank or Isnumber or whatever.
    Any help will be greatly appreciated.

    An example of my worksheet, the data being analysed is row C5:U5 compared with C6:U6
    ******** ******************** ************************************************************************>
    Microsoft Excel - Footy Tables Rev2a.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    5
    Arsenal*H2012222052421431223
    6
    A0001111001211121010
    7
    Aston*VillaH0201230323010002113
    8
    A2221120001012201002
    04 - 05 SCORES*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    This is where the formulas are, all are fine if the data range contains data in every cell. However, when i clear the contents to input new data my formula for counting draws, cell AM5, is counting blanks. Not what i want.
    Hope i've made things clear, thanks in advance.
    ******** ******************** ************************************************************************>
    Microsoft Excel - Footy Tables Rev2a.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    AI
    AJ
    AK
    AL
    AM
    AN
    3
    *TOTH*WH*LDraw*
    4
    ******
    5
    *191504*
    6
    **79%0%21%*
    7
    *19946*
    04 - 05 SCORES*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    What happens when there is no data, Cell AM5 shows 19 draws. Not what i want.
    ******** ******************** ************************************************************************>
    Microsoft Excel - Footy Tables Rev2a.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    AI
    AJ
    AK
    AL
    AM
    AN
    3
    *TOTH*WH*LDraw*
    4
    ******
    5
    *190019*
    6
    **0%0%100%*
    7
    *19946*
    04 - 05 SCORES*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Make Formula ignore blank cells

    {=SUM(IF(C5:U5=C6:U6,1,0))- COUNTIF(C5:U5,0)}

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  3. #3
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,450

    Default

    Hi,

    How about a non-array entered function ...

    =SUMPRODUCT(--(C5:U5>C6:U6),C5:U5)

    (Hi Nimrod!)
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Make Formula ignore blank cells

    Hi firefytr

    Nice solution

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    New Member
    Join Date
    Aug 2004
    Posts
    22

    Default Re: Make Formula ignore blank cells

    Hi, thanks for your suggestions, however, they do not give the result i want.
    I tried your solution fireftyr but when i filled the range with test data in which there is four draws (so the formula should return 4) i got the return of 38!
    I also tried your solution Nimrod but the value return is 2. I checked the test data and there are 2 instances of a draw being 0 0. See below, cells D5&D6 and J5&J6. Is this the reason why the return value is 2 and not 4? Does the formula need to be revised? Is it ignoring instances where the cells are 0 0.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Footy Tables Rev2a.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    4
    ********************
    5
    H2012222052421431223
    6
    A0001111001211121010
    7
    H0201230323010002113
    8
    A2221120001012201002
    04 - 05 SCORES*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    And your result Nimrod
    ******** ******************** ************************************************************************>
    Microsoft Excel - Footy Tables Rev2a.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    AI
    AJ
    AK
    AL
    AM
    AN
    3
    *TOTH*WH*LDraw*
    4
    ******
    5
    *171502*
    6
    **88%0%12%*
    7
    *19946*
    8
    **47%21%32%*
    04 - 05 SCORES*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    Hope you can help
    Thanks people.

  6. #6
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,450

    Default

    To just count, something like ...

    =SUMPRODUCT(--(C5:U5>C6:U6),--(ISNUMBER(C5:U5)))

    And in your above example, it's 15, not four - at least with your example.
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  7. #7
    New Member
    Join Date
    Aug 2004
    Posts
    22

    Default Re: Make Formula ignore blank cells

    Still not sure what you are getting at firefytr, tried your suggestion but still not working as i want. Not sure if i have explained myself clearly as to what i'm trying to achieve and whether this is proving confusing.
    Also, the return i am looking for is 4 and not the 15 you see reurned. If you look at the example below i have highlighted the cells of data in question. Comparing the value of cells in top row with those in the row underneath on a column by column basis. Thus, there are 19 comparrisons in all.
    ******** ******************** ************************************************************************>
    Microsoft Excel - Footy Tables Rev2a.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    7
    0201230323010002113
    8
    2221120001012201002
    04 - 05 SCORES*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Now, the calculations are undertaken in the following area. As you can see, using the data above returns the results below. Formula in Cell AK5 has calculated 15 instances where the top row cell is greater than the lower row, thus 15 home wins in this case. There are no Home losses, (Cell AL5) and four draws(Cell AM5).
    ******** ******************** ************************************************************************>
    Microsoft Excel - Footy Tables Rev2a.xls___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    AJ
    AK
    AL
    AM
    3
    TOTH*WH*LDraw
    4
    ****
    5
    191504
    04 - 05 SCORES*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    This all works fine when the data range is full but as i've said earlier the formula for calculating draws breaks down when there is no or just a few entries in, counting blank cells as a match. Applying the suggestions from yourself and Nimrod and variations thereof are not quite what i'm looking for, although Nimrods suggestion seemed to work up to a point, i.e. it recognised matches where the cell contents was 1 or above, but not where the contents was 0 & 0.
    Sorry if i seem to be going on a bit or being a pest, just that i'm sure there is a simple solution to this frustrating problem. Thanks for your patience.
    Cheers

  8. #8
    New Member
    Join Date
    Aug 2004
    Posts
    22

    Default Re: Make Formula ignore blank cells

    Just thought i'd add that my Htmlmaker does not seem to showing the formulas in individual cells, just seems to be stuck on one for some reason.

  9. #9
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,450

    Default

    Ok, so you're looking to compare the two ranges and count only the pairs that EQUAL each other, but not zero? If that is the case, try ...


    =SUMPRODUCT(--(C5:U5=C6:U6),--(C5:U5<>0))
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  10. #10
    New Member
    Join Date
    Aug 2004
    Posts
    22

    Default Re: Make Formula ignore blank cells

    Nearly there, looking to compare the two ranges and count only the pairs that EQUAL each other, but INCLUDE zero (nil nil or zero zero is a valid score) but NOT empty cells. At the moment the ranges are full of data, in real usage they start off blank and will have entries added week by week, so blank cells have to be accounted for.
    Thanks

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com