+ Reply to Thread
Results 1 to 16 of 16

FALSE output from table formulae

  1. #1
    Registered User
    Join Date
    06-14-2005
    Location
    London
    MS-Off Ver
    2016
    Posts
    77

    FALSE output from table formulae

    I have an excel table which produces stats on individuals.

    One of the formulae is

    =IF(OR(tblIncidentData[@State]="Closed",tblIncidentData[@State]="Resolved"),COUNTIFS(tblIncidentData[Assignee], "="&[@Analyst], tblIncidentData[Resolved D&T], ">="&$D$2, tblIncidentData[Resolved D&T], "<="&$F$2, tblIncidentData[_Chargeable],"=Chargeable"))

    Where @Analyst contains the individuals name.

    So I have 8 rows that it works perfectly on and reports a number, I then have one row, with exactly the same formulae, which has FALSE and I have no idea why, what i do no is

    i) If i use the analyst name on the row below, it works fine
    ii) The cell is formatted exactly the same
    iii) If i put someone elses name in there, I get the same error.
    iv) I've deleted the row from the table, which obviously causes the one below to move up, and that then produces a FALSE.
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: FALSE output from table formulae

    If the OR formula is TRUE then a COUNTIFS is performed which can only return a number, otherwise...

    ...well there is no otherwise since you don't have a formula for when the result of the OR is not TRUE.
    Therefore you're getting a FALSE, hence the OR is failing so the IncidentData for @ State is neither "Closed" nor "Resolved", must be some other value.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    06-14-2005
    Location
    London
    MS-Off Ver
    2016
    Posts
    77

    Re: FALSE output from table formulae

    Thanks Special K, so what is the better way to do this then? Why does this work for other people on the list? why does it work for this one individual if I put him on another row, as per the example in my initial post?

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: FALSE output from table formulae

    Unsure without the data, can you post the file? Desensitise if it necessary.

  5. #5
    Registered User
    Join Date
    06-14-2005
    Location
    London
    MS-Off Ver
    2016
    Posts
    77

    Re: FALSE output from table formulae

    Do you mean just the worksheet that has the table on it, or also the data that sits behind it? If it's the data that would take quite a bit of sensitising

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: FALSE output from table formulae

    I think it's the data that's causing the problem.

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: FALSE output from table formulae

    When you see FALSE, what is in the State column? As Special-K mentioned earlier, it cannot be either "Closed" or "Resolved".
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  8. #8
    Registered User
    Join Date
    06-14-2005
    Location
    London
    MS-Off Ver
    2016
    Posts
    77

    Re: FALSE output from table formulae

    If i put that analyst name on a different row of the table, then i do not get a false, i get the figure I expect - 93.

    On this row of the table, regardless of the analyst name i use, it always give a FALSE. I can use the same analyst name on the row above or below that line and i get the figures i would expect for them.

    So I can't see it is the data.

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: FALSE output from table formulae

    You still haven't answered the question. What is in the State column for that row?

    The way your formula is written, if the State column for a given row doesn't contain either "Closed" or "Resolved, the formula will return FALSE, no matter what.

  10. #10
    Registered User
    Join Date
    06-14-2005
    Location
    London
    MS-Off Ver
    2016
    Posts
    77

    Re: FALSE output from table formulae

    Sorry, what do you mean the state column for that row? State is a column within the datasheet, the query is looking for any records that have a STATE of either Resolved or Closed, between two dates and is Assigned to @Analyst in the table.

  11. #11
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: FALSE output from table formulae

    Then the State on that row does NOT contain "Resolved" or "Closed" (regardless of how it looks to you) or you would have a COUNTIF result, ie a number. like the rest of the rows.
    The fact you have FALSE is because the IF condition has failed and you haven't supplied a value for when it fails.
    The If condition will only fail if the row does not contain "Resolved" or "Closed"

    Have a close look at the State value on that row.
    In particular do this

    in a blank row
    =LEN(cell)
    where cell is the State cell on that FALSE row

    "Resolved" is 8 characters
    "Closed" is 6 characters

    I suspect you will get neither 6 nor 8 for that row indicating the value is neither Resolved nor Closed.

    Check any other rows that contain FALSE as well.

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: FALSE output from table formulae

    I mean, what is in the State column on the row where your formula outputs FALSE?

    Your formula says:
    If the State column contains Resolved or Closed, then do the Countifs part, but it does not say what to do if the State column does not contain either of those values so in that case Excel simply returns its default FALSE value. Essentially you have only provided two of the arguments to the IF function, which expects 3.

  13. #13
    Registered User
    Join Date
    06-14-2005
    Location
    London
    MS-Off Ver
    2016
    Posts
    77

    Re: FALSE output from table formulae

    XInitwit - the state column is on the datasheet which is seperate to the sheet this table is in. Across the whole datasheet, in the state column, not only is there resolved or closed but also Open, In Progress etc.

    I get the point that the OR statement doesn't say what to do if it is not resolved or closed

    What i don't get is
    a) Why the exact same query does not fail on the other rows of my table whether I use the name of the individual that is shown against the FALSE originally, or anyone elses name. The formulae used on that row of my table fails regardless of the individual name used,

  14. #14
    Registered User
    Join Date
    06-14-2005
    Location
    London
    MS-Off Ver
    2016
    Posts
    77

    Re: FALSE output from table formulae

    Also if I recreate the table, anywhere else on the worksheet, it works fine.

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: FALSE output from table formulae

    Your formula is using @State which means the State column in the same row as the formula. If it's a separate table, that makes no sense to me and Im not really sure what you are trying to achieve?

  16. #16
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    6,997

    Re: FALSE output from table formulae

    With 15 replies to what seems like a small problem
    this is the time to supply a spreadsheet so we can look at it in depth.

    Desensitise if it necessary.

+ 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 to Remove False from output
    By cndu in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-01-2015, 02:31 AM
  2. [SOLVED] How to get 0 instead of word 'False' in formula output?
    By mso3 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-26-2015, 01:18 AM
  3. Formulae Input/output help please
    By bimo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-03-2014, 02:52 PM
  4. How to add a Rule to output TRUE/FALSE into a column ?
    By zerodegree in forum Excel General
    Replies: 13
    Last Post: 10-25-2014, 12:08 PM
  5. false values returned when I copy formulae from one cell to another
    By brian.ward in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-08-2014, 06:28 AM
  6. [SOLVED] Alternative to IF: need to perform different formulae for True and False
    By illyBLC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-25-2013, 09:04 AM
  7. Change default checkbox output true false into text
    By safonso in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-02-2013, 03:31 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