# Thread: Help with monstrous formula from H*ll

1. Registered User
Join Date
Sep 2009
Posts
2

## 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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
I'm afraid I just do not understand!
Perhaps you can provide us with sample data and expected output to help illustrate your problem

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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.

4. Registered User
Join Date
May 2005
Posts
129
Wolgie

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
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
•