Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009

    Red face Unanswered: Help with monstrous formula from H*ll

    I am trying to solve a problem here is the criteria: I have a number of cells (9 in total) that require a score of 1-3. They will effect the total score for cell AB21.

    I have a count function counting all the number 1's in cell AB40
    and a count function counting all the number 2's in AC40

    These are the only two numeric values that affect the outcome. And matter.
    Cell AB21 will have a possible value of 1-4
    The criteria are as follows a rating(value) of 4 = no more than 1 (number 1) in cell ab40. And no (number 2's) in ac40

    a rating of 3 = 1 (number 1) and 0-1 (number 2's) or 0 (number 1's) and 2-3 (number 2's)

    a rating of 2 = 2 (number 1's) and 0-1 (number 2's) or 1 (number 1) and 2-3 (number 2's) or 0 (number 1's) and 4+ (number 2's)

    a rating of 1 = 3+ (number 1's) or 6+ (number 2's) or 2 (number 1's) and 2+ (number 2's) or 1 (number 1) and 4+ (number 2's)

    The formula I am attemting to use is as follows:
    =IF(AND(AB40<1,AC40<=1),4,IF(AND(AB40=1,AC40<=1),3 ,IF(AND(AB40=0,AC40>1<=3),3,if(and(AB40=2,ac40<=1) ,2,if(and(ab40=1,ac40>=2<=3),2,if(and(ab40=0,ac40= 4),2,if(ab40>3,1,if(ac40>6,1,if(and(ab40=2,ac40>=2 ),1,if(and(ab40=1,ac40>=4,1)))))))))

    I am probably making this more difficult than it has to be, and you probably have no idea what I am trying to do after reading this. But if you have any ideas I would love to hear them.

    Thanks for the help

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    I'm afraid I just do not understand!
    Perhaps you can provide us with sample data and expected output to help illustrate your problem
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    historically Ive always found it better to do this sort of thing in small steps
    so I'd assign the rating 1,2 or 3 to an individual section
    if you need to do further processing then do that in cells for each row
    then combine those ratings in your final cell

    one of the problems I find with spreadsheets is that its very easy to make a change in one place, expecting the app to pick up thoise chanegs, but its very easy to make chanegs, destroy the link behind the scenes and not notice your app is now broken.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2005
    Provided Answers: 1

    If I have understood your question, this is the basis of the formula you require

    But due to the limitation of excel only allowing 7 If staments I had to split out the rating

    Cell A25 =IF(AND(OR(B14=2,B14=3),A14=0),3,IF(AND(OR(B14=0,B 14=1),A14=1),3,IF(AND(A14=0,B14>=4),2,IF(AND(OR(B1 4=2,B14=3),A14=1),2,IF(AND(OR(B14=0,B14=1),A14=2), 2,0)))))

    Cell B25 =IF(AND(A14=2,B14>=2),1,IF(AND(A14=1,B14>=4),1,IF( AND(A14>=3,B14>=6),1,0)))

    Merge the two above

    Cell A29 =IF(AND(A25>=2,B25=0),A25,IF(AND(B25=1,A25<=0),B25 ,""))

    See attached file, in my example Cell ref A29 is the Resulting Rating

    Hope this helps
    Attached Files Attached Files
    Last edited by MarkWhyte; 09-15-09 at 10:28.

Posting Permissions

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