+ Reply to Thread
Results 1 to 6 of 6

If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 or 5

  1. #1
    Registered User
    Join Date
    08-15-2019
    Location
    England
    MS-Off Ver
    Several different types
    Posts
    7

    If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 or 5

    Hi all,

    I have a score predictor game in a spreadsheet (see attached), and I have a formula in to work the points out already e.g. if the actual score is 1-1 and the person predicted the correct score exactly (e.g. 1-1), then they get 5 points, if they predicted the correct result (e.g. 2-2) then they get 2 points, or if they predicted in this case, a win for either time, then they would get 0 points.

    My problem is that I have entered people's predictions (please see Sheet1, table titled Week 2 - I have entered 'Zak W' and 'Luke B' score predictions) and the games haven't been played yet so the actual results are blank. However, the total points adds up to 2 whenever someone has predicted a score draw, or if someone has predicted 0-0, it automatically gives them 5 points. How can I make these points stay at '0' until I have entered the actual results as well e.g. something along the lines of 'if actual scores blank, points = 0'.

    Here is my current formula which works minus the problem I have:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hopefully I made sense and I look forward to any assistance.

    Thank you so much, Zak
    Attached Files Attached Files
    Last edited by zakwebb47; 08-18-2019 at 01:58 PM. Reason: Solved

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,075

    Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

    Like this:
    =IF(OR(actual scores = "",(D16>E16)-(F16>G16),(D16<E16)-(F16<G16)),0,IF(AND(D16=F16,E16=G16),5,2))
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    08-15-2019
    Location
    England
    MS-Off Ver
    Several different types
    Posts
    7

    Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

    Hi Paul, thanks for your quick response. I tried playing with that and couldn't make it work. I probably sound really stupid now, but I tried replacing actual scores with D16,E16 to show the boxes that if blank, then = 0 but it comes up with #NAME? or when I thought I got it to work, it lost the formula for making it 2/5 points with correct result/score.

    Any tips?

    Many thanks again

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,371

    Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

    Part of your problem is that you typed in "-" instead of just leaving the cells empty.
    In H16, etc, use:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 08-16-2019 at 04:07 PM.
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    08-15-2019
    Location
    England
    MS-Off Ver
    Several different types
    Posts
    7

    Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

    Ah yes that formula works much better, thank you! The only thing, it's not making the Points box show '0', it's showing blank which is affecting my totals making them show #VALUE! because the boxes have no numbers in...I don't see in the formula you gave me what would stop it filling a 0 into the box...hmmm

    Thanks for your help

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,371

    Re: If 2 results don't match, how can I make a 'Total' cell show 0, right now it shows 2 o

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how can i make a chart show how close a value is to a total amount
    By kevinu in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-24-2019, 09:25 PM
  2. Make a list that shows up when a cell is clicked
    By iiro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2018, 02:20 AM
  3. Is there way to make a pivot table not show lines where a total is zero?
    By DanMinalt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2009, 10:38 PM
  4. Formula BOx shows Results but Cell Does not
    By Dcdrj2 in forum Excel General
    Replies: 4
    Last Post: 03-23-2007, 07:20 PM
  5. Re: make cell show blank when total is zero
    By Bob L in forum Excel General
    Replies: 3
    Last Post: 05-19-2006, 12:04 PM
  6. Replies: 1
    Last Post: 01-08-2005, 04:06 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1