+ Reply to Thread
Results 1 to 12 of 12

Blanks cells treated as zeros

  1. 10-21-2009, 07:15 PM #1
    junada0
    junada0 is offline
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Blanks cells treated as zeros

    Hi,

    i am creating a football prediction sheet, and have a problem where blank cells are treated as zeros.
    The enclosed spreadsheet shows the formulas in green working fine, but the blanks are treated as zeros in cells j6, I7 & J7.

    Would appreciate any help.

    G

    Thanks for all your help, this works fine.

    G
    Attached Files Attached Files
    Last edited by junada0; 10-22-2009 at 04:22 PM.
    Reply With QuoteRegister To Reply
  2. 10-21-2009, 08:34 PM #2
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,375

    Re: Blanks cells treated as zeros

    Hi,

    if you compare a 0 with a blank, the = comparison will return TRUE

    but if you use the EXACT() formula, =EXACT(0,"") it will return FALSE/ So I guess in your case

    =IF(OR(ISBLANK(G7),ISBLANK(H7)),"",IF(AND(EXACT(G7,J7),EXACT(H7,K7)),3,IF(SIGN(G7-H7)=SIGN(J7-K7),1,0)))

    and copy down
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    Reply With QuoteRegister To Reply
  3. 10-21-2009, 08:48 PM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Blanks cells treated as zeros

    Or maybe in M7 and down,

    =IF(COUNT(J7:K7)<>2, 0, 2*(SIGN(J7-K7)=SIGN(G7-H7)) + AND(G7:H7=J7:K7) )

    EDIT: Confirmed with Ctrl+Shif+Enter, or

    =IF(COUNT(J7:K7)<>2, 0, 2*(SIGN(J7-K7)=SIGN(G7-H7)) + (G7=J7)*(H7=K7) )

    confirmed with just Enter.
    Last edited by shg; 10-22-2009 at 10:38 AM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  4. 10-22-2009, 01:30 PM #4
    junada0
    junada0 is offline
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Blanks cells treated as zeros

    Hi,

    Thanks for you reply, however when I copied =IF(OR(ISBLANK(G7),ISBLANK(H7)),"",IF(AND(EXACT(G7,J7),EXACT(H7,K7)),3,IF(SIGN(G7-H7)=SIGN(J7-K7),1,0)))

    I get the same issue, where blanks are zero's.

    Also tried the following recommendation but a win shows as 2 points instead of 3.

    =IF(COUNT(J7:K7)<>2, 0, 2*(SIGN(J7-K7)=SIGN(G7-H7)) + AND(G7:H7=J7:K7) )

    and

    =IF(COUNT(J7:K7)<>2, 0, 2*(SIGN(J7-K7)=SIGN(G7-H7)) + (G7=J7)*(H7=K7) )

    Could you copy into a spreadsheet and return, many thanks.
    G
    Reply With QuoteRegister To Reply
  5. 10-22-2009, 03:24 PM #5
    junada0
    junada0 is offline
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Blanks cells treated as zeros

    Just realized the formula below does work, does anyone know how to edit this so that correct result equals 1 point instead of 2 ponts, and keep the correct result as 3 points.


    =IF(COUNT(J7:K7)<>2, 0, 2*(SIGN(J7-K7)=SIGN(G7-H7)) + (G7=J7)*(H7=K7) )

    Sorry for any confusion, appreciate your help
    Reply With QuoteRegister To Reply
  6. 10-22-2009, 03:42 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Blanks cells treated as zeros

    so that correct result equals 1 point instead of 2 ponts, and keep the correct result as 3 points
    Say again?
    Reply With QuoteRegister To Reply
  7. 10-22-2009, 03:56 PM #7
    junada0
    junada0 is offline
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Blanks cells treated as zeros

    Accept my appolgy, for not being clear,



    If prediction is 1-0 and result is 1-0 show 3 points.
    If prediction is 1-0 and result is 2-0 show 1 point.(formula shows 2 points)
    If prediction is 1-1 and result is 1-1 show 3 points.
    If prediction is 1-1 and result is 2-2 show 1 point. (formula shows 2 points)
    If prediction is 1-1 and result is 1-2 show 0 points


    Thanks
    G
    Reply With QuoteRegister To Reply
  8. 10-22-2009, 03:58 PM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Blanks cells treated as zeros

    =if(count(j7:k7)<>2, 0, (sign(j7-k7)=sign(g7-h7)) + 2*(g7=j7)*(h7=k7) )
    Reply With QuoteRegister To Reply
  9. 10-22-2009, 04:23 PM #9
    junada0
    junada0 is offline
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Blanks cells treated as zeros

    Thanks, this works perfectly.

    G
    Reply With QuoteRegister To Reply
  10. 10-22-2009, 06:55 PM #10
    junada0
    junada0 is offline
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Blanks cells treated as zeros

    Hi,

    I thought that this was fully solved, but have just noticed a slight error.

    If the result is left blank and the prediction is shown as a draw i.e 0-0 1-1 etc then 1 point is displayed.

    =if(count(j7:k7)<>2, 0, (sign(j7-k7)=sign(g7-h7)) + 2*(g7=j7)*(h7=k7) )

    Any ideas to stop blank cells showing as values.

    G
    Reply With QuoteRegister To Reply
  11. 10-22-2009, 07:30 PM #11
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Blanks cells treated as zeros

    =if(count(g7:h7, j7:k7)<>4, "", (sign(j7-k7)=sign(g7-h7)) + 2*(g7=j7)*(h7=k7) )
    Reply With QuoteRegister To Reply
  12. 10-22-2009, 07:40 PM #12
    junada0
    junada0 is offline
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Blanks cells treated as zeros

    Hi,

    Looks good, appreciate your help..
    G
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1