Results 1 to 4 of 4

091109, 15:18 #1Registered 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 13. 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 14
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 01 (number 2's) or 0 (number 1's) and 23 (number 2's)
a rating of 2 = 2 (number 1's) and 01 (number 2's) or 1 (number 1) and 23 (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

091209, 04:05 #2www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,422
Provided Answers: 10

091209, 04:49 #3Jaded Developer
 Join Date
 Nov 2004
 Location
 out on a limb
 Posts
 13,692
Provided Answers: 59historically 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

091509, 07:29 #4Registered User
 Join Date
 May 2005
 Posts
 124
Provided Answers: 1Wolgie
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 helpsLast edited by MarkWhyte; 091509 at 09:28.