Help Needed to Simplify Formula with Multiple Cominations with Multiple Variables

[xtreme07]

Hi,

i have a formula that needs to be simplified in excel as there's many variables and i would end up spending a lot of time on it.

this is the formula
=
IF(AND(A1<=10,B1<=10,C1<=10,D1<=10),"1.1.1.1"
IF(AND(A1>=20,B1<=10,C2<=10,D1<=10),"2.1.1.1",
,"FALSE")

the same formula has more lines, in which A1 needs to meet some more numbers criteria. i've just decided to have it short to outline the basics of it.

as you might have noticed, there's 3 PRINT modes:
PRINT1 = 1.1.1.1
PRINT2 = 2.1.1.1
PRINT3 = FALSE

in PRINT2, the A1 criteria has changed, which is why i asked that it will PRINT 2.1.1.1 if the criteria is true
else, if the criteria is not true, it will PRINT false (PRINT3)

THE ISSUE that i have is that this formula that i'm using will have many combinations .. about 15 combinations if not more.
WONDERING if there's a way i can ease my pain in excel.

excel like	A	B	C	D	E
1	1	6	11	12
2	10	19	28	40
3	4	58	17	18
4	22	42	25	32
5	32	33	47	73
6	45	9	36	91

<tbody>
</tbody>

 

[xtreme07]

another simplified version would be to think of a matrice

a b
c d

equivalent to

1 2
3 4

where a,b,cd represent the conditions
and number 1,2,3,4 represent the output, based on whether the conditions are met or not
also, the output having values of 1-4 for ach letter

for example A can have the output 1, or 2, or 3, or 4
and the same for the B, C, D

that's because A condition ca be varied as A1, A2, A3, A4
B condition having 4 variables as well, B1, B2, B3, B4

 

[njimack]

I'm afraid what you've written is incomprehensible to me. Can't you post some real-life examples of your data together with the desired result?

 

[xtreme07]

download file link is external - on mediafire.com
click this to download

i'm using external link cause i can't see any atachement option ...
this is the same file i've provided yesterday.

to download, go to the external link and click download button from the top of the page
to open the file excel 2007 or later is needed. i tried to save it as 2003 version but there's compatibility issues.

let me know if it works for you

here's the formula used in the file

=
IF(AND(B2<=60,C2<=40,[U](D2+E2)<=   10[/U]),"1.1.[U]1[/U]",
IF(AND(B2<=60,C2<=40,[U](D2+E2)<=   20)[/U],"1.1.[U]2[/U]",
IF(AND(B2<=60,C2<=40,[U](D2+E2)<= 1000)[/U],"1.1.[U]3[/U]",

IF(AND([U]B2> 60[/U],C2<=40,(D2+E2)<=   10),"[U]2[/U].1.1",
IF(AND([U]B2> 60[/U],C2<=40,(D2+E2)<=   20),"[U]2[/U].1.2",
IF(AND([U]B2> 60[/U],C2<=40,(D2+E2)<= 1000),"[U]2[/U].1.3",

IF(AND(B2> 60,[U]C2> 40[/U],(D2+E2)<=   10),"2.[U]2[/U].1",
IF(AND(B2> 60,[U]C2> 40[/U],(D2+E2)<=   20),"2.[U]2[/U].2",
IF(AND(B2> 60,[U]C2> 40[/U],(D2+E2)<=  100),"2.[U]2[/U].3",
"FALSE")))))))))

UNDERLINED is where the formula has changed compared to the previous rows

 

[xtreme07]

if you feel like you'r spending to much time on it, it's ok to quit this one.
i don't mind ... not saying i wouldn't like to have this solved ...but i don't what you to be spending to much time on something that's to hard to understand.

 

[xtreme07]

below you can see live example


Excel 2007

	A	B	C	D	E	F
1	keywords	da	pa	ifl	efl	forumla for b, c, d, e
2	occupational safety and health administration	1	1			1.1.1
3	rapid rewards enrollment promotion code	1	1			1.1.1
4	dating site in usa	19	33	5	15	1.1.2
5	bali cashews	11	23	14	1	1.1.2
6	love and basketball full movie	22	16	13	0	1.1.2
7	free royalty free images	60	34	12	0	1.1.2
8	what is a life coach	34	39	19	39	1.1.3
9	sunday dinner ideas	47	28	359	2	1.1.3
10	aliments riches en vitamine d	45	19	85	0	1.1.3
11	short term effects of smoking	86	35	0	3	2.1.1
12	home remedies for glowing skin	90	29	2	0	2.1.1
13	how to reduce hair fall	94	1			2.1.1
14	how to stop hair fall	94	1			2.1.1
15	home remedies for hair fall	82	1			2.1.1
16	weight loss workouts	77	5	0	16	2.1.2
17	gifts for husband	77	40	1	15	2.1.2
18	blood clot in leg symptoms	92	32	13	1	2.1.2
19	bondara voucher code	63	25	13	0	2.1.2
20	black and decker parts	74	32	12	0	2.1.2
21	cheap halloween costumes	64	27	11	0	2.1.2
22	pre diabetes diet	61	9	644	11	2.1.3
23	ice cream games for girls	65	31	161	0	2.1.3
24	ice cream games	65	1	161	0	2.1.3
25	gluten free cornbread	70	1	30	0	2.1.3
26	wiggle discount code	63	29	29	0	2.1.3
27	angie s list promo code	85	46	0	8	2.2.1
28	almond butter vs peanut butter	79	43	0	5	2.2.1
29	how to make a candle	92	47	4	4	2.2.1
30	how does solar power work	95	46	0	0	2.2.1
31	how does solar energy work	95	46	0	0	2.2.1
32	what is a balanced diet	93	58	2	18	2.2.2
33	how to check blood pressure	96	56	4	16	2.2.2
34	symptoms of low blood sugar	96	47	12	1	2.2.2
35	how to make curry	92	46	19	0	2.2.2
36	creatine side effects	90	63	7	89	2.2.3
37	brussel sprouts nutrition	88	63	8	82	2.2.3
38	home remedies for yeast infection	92	63	12	77	2.2.3
39	victoria sponge cake	80	57	34	64	2.2.3
40	sweet potato soup	93	62	5	61	2.2.3
41	healthy blueberry muffins	81	46	123	7	2.2.3
42	ice cream factory	45	53	6	381	FALSE
43	green star juicer	39	48	4	380	FALSE
44	frozen yogurt machine	49	51	196	173	FALSE
45	oatmeal cookies -raisins	32	42	2	50	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =IF(AND(B2<=60,C2<=40,(D2+E2)<=10),"1.1.1", IF(AND(B2<=60,C2<=40,(D2+E2)<=20),"1.1.2", IF(AND(B2<=60,C2<=40,(D2+E2)<=1000),"1.1.3", IF(AND(B2>60,C2<=40,(D2+E2)<=10),"2.1.1", IF(AND(B2>60,C2<=40,(D2+E2)<=20),"2.1.2", IF(AND(B2>60,C2<=40,(D2+E2)<=1000),"2.1.3", IF(AND(B2>60,C2>40,(D2+E2)<=10),"2.2.1", IF(AND(B2>60,C2>40,(D2+E2)<=20),"2.2.2", IF(AND(B2>60,C2>40,(D2+E2)<=1000),"2.2.3", "FALSE"))))))))) <tbody> </tbody>

<tbody>
</tbody>

 

[njimack]

Now we're getting somewhere!!

Based on your sample of data, the below formula returns the correct result for all rows apart from the last 4. It's much shorter and simpler than the nested IF's and AND's you've been using. I'll post back when I have a fully working version.

=IF(B2<=60,1,2)&"."&IF(C2<=40,1,2)&"."&LOOKUP(D2+E2,{0,11,21},{1,2,3})

One question - does the formula actually have to contain a decimal point between each digit, or could we simply return a string of numbers which are formatted so that they appear to be separated by a decimal point?

 

[njimack]

And here's the fully working formula. First version includes decimal points between each digit, the 2nd doesn't, but can appear the same if you use custom formatting...
=IF(OR(D2+E2>1000,AND(B2<=60,C2>40)),"FALSE",IF(B2<=60,1,2)&"."&IF(C2<=40,1,2)&"."&LOOKUP(D2+E2,{0,11,21},{1,2,3}))
=IF(OR(D2+E2>1000,AND(B2<=60,C2>40)),"FALSE",(IF(B2<=60,1,2)&IF(C2<=40,1,2)&LOOKUP(D2+E2,{0,11,21},{1,2,3}))+0)
(formatted as 0"."0"."0)

 

[xtreme07]

cool .. it works ... so, THANK YOU Very Much ...i would probably kiss you if you were next to me ) (i'll pretend to be a woman in case you'r picturing me kissing you)

it must have been a grueling thread it was for me for sure ...
everything feels so good now ...

next thing on my to do list is to understand it.
can you tell me what to study to understand the theory behind this ?

to answer your question on the decimal point ...the result needs a decimal point 'cause (from what i remember) it helps with sorting largest to smallest (and vice versa).

 

[njimack]

Glad we got there in the end, but I'll pass on the kiss if you don't mind!

The final function isn't particularly complicated. The key was to break down the end result into 3 separate digits, which allowed me to condense the duplicated parts of your formula. For example, your first 3 IF statements all tested B2 <=60, and they all resulted in a string of digits where the first character is 1. Therefore, I converted this into IF(B2<=60,1,2). I did exactly the same with IF(C2<=40,1,2).

 

[xtreme07]

one last question ... now that the forumla is completed i, need one more adjustment.

how do we add another factor (another column with numbers to analize, and print a value based on it) ?