+ Reply to Thread
Results 1 to 3 of 3

Inconsistent results for formula

  1. #1
    Registered User
    Join Date
    12-18-2013
    Location
    Alberta
    MS-Off Ver
    Excel 2010
    Posts
    12

    Inconsistent results for formula

    I have a spreadsheet of individuals with columns as follow: first name, surname, age and then five columns broken out into various age groups: 21-35, 36-50, 51-65, 65-78 and 78+. Ages automatically come from another sheet (labelled MASTER), brought over with, eg =' MASTER '!H36.

    What I want is for the sheet to show an 'X' in the proper age group column for each individual, based on the age shown.

    I used the formula, (say for the individual in row 4) =IF((C4>36)*AND(C4<51),"X",""), with appropriate amendments for age groups for each column and row numbers for each individual.

    95% of the time, it works perfectly. For the rest, nothing happens. It's a blank row, with no 'X' showing anywhere. I have reformatted them for Numbers General, checked the text colour to make sure it wasn't showing in white or something, etc. The formula is precisely the same in the empty boxes as the ones above and below them (less row number, of course).

    Any suggestions, please?
    Last edited by Maxwel; 02-27-2014 at 11:53 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Inconsistent results for formula

    You need to check the contents of column C and in column H of your master sheet - perhaps some of your ages have come across as text values which look like numbers. If you attach a sample workbook then we can check into it further for you.

    Hope this helps.

    Pete

    By the way, you don't construct an AND function like that - you should have:

    IF(AND(condition_1,condition_2), "X","")

  3. #3
    Registered User
    Join Date
    12-18-2013
    Location
    Alberta
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Inconsistent results for formula

    Thank you. That seems to have fixed it. Not sure where I got the other formula, to be honest.

    Much appreciated.

+ 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. [SOLVED] Inconsistent if(and(or results, HELP!!
    By Groovicles in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-03-2013, 11:50 AM
  2. Excel 2007 : Inconsistent results with Lookup
    By chuckbent in forum Excel General
    Replies: 12
    Last Post: 08-21-2011, 08:11 PM
  3. Inconsistent IF Results
    By BW29 in forum Excel General
    Replies: 3
    Last Post: 12-20-2010, 10:12 AM
  4. Inconsistent Forumla Error
    By dhbeisner in forum Excel General
    Replies: 1
    Last Post: 02-21-2008, 07:49 PM
  5. Replacing a cell's forumla with the forumla's results?
    By Mooncalf in forum Excel General
    Replies: 2
    Last Post: 01-04-2005, 12:35 AM

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