ROUNDUP and MIN with IF/AND

[Darren_workforce]

=ROUND(MIN(IF(AND(Jan!$B:$B>=$O$22,Jan!$C:$C>=$O$52),Jan!$A:$A,0)*Jan!$A:$A,0),0)

This is my latest attempt at a formula that doesn't appear to work. If I don't include the AND statement, I can get the data to populate just fine. But I need 2 conditions to be true. First, when searching Col B, the results need to be above whatever variable is entered into cell O22. Also, when searching Col C, we are looking for anything greater than the variable in O52.

I would like the lowest value in Col A to be generated based on the above 2 requirements. (EX: If O22=97% and O52=52, this would result in 23. What is wrong with the formula?

Agents	SVL	Shrink
1​	0.093207​	31​
2​	0.257581​	32​
3​	0.392158​	33​
4​	0.502341​	34​
5​	0.592551​	35​
6​	0.666409​	36​
7​	0.726879​	37​
8​	0.776387​	38​
9​	0.816921​	39​
10​	0.850108​	40​
11​	0.877279​	41​
12​	0.899524​	42​
13​	0.917737​	43​
14​	0.932649​	44​
15​	0.944858​	45​
16​	0.954853​	46​
17​	0.963037​	47​
18​	0.969737​	48​
19​	0.975223​	49​
20​	0.979714​	50​
21​	0.983391​	51​
22​	0.986402​	52​
23​	0.988867​	53​
24​	0.990885​	54​
25​	0.992537​	55​
26​	0.99389​	56​
27​	0.994998​	57​

 

[dreid1011]

How is it 23? Just by testing the AND() conditions alone would return 19 as the lowest.

Book2
	A	B	C	D	E	F	G
1	Agents	SVL	Shrink
2	1	0.093207	31		0	FALSE	19
3	2	0.257581	32			FALSE	22
4	3	0.392158	33			FALSE	23
5	4	0.502341	34			FALSE	24
6	5	0.592551	35			FALSE	25
7	6	0.666409	36			FALSE	26
8	7	0.726879	37			FALSE	27
9	8	0.776387	38			FALSE
10	9	0.816921	39			FALSE
11	10	0.850108	40			FALSE
12	11	0.877279	41			FALSE
13	12	0.899524	42			FALSE
14	13	0.917737	43			FALSE
15	14	0.932649	44			FALSE
16	15	0.944858	45			FALSE
17	16	0.954853	46			FALSE
18	17	0.963037	47			FALSE
19	18	0.969737	48			FALSE
20	19	0.975223	59			TRUE
21	20	0.979714	50			FALSE
22	21	0.983391	51			FALSE
23	22	0.986402	52			TRUE
24	23	0.988867	53			TRUE
25	24	0.990885	54			TRUE
26	25	0.992537	55			TRUE
27	26	0.99389	56			TRUE
28	27	0.994998	57			TRUE
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROUND(MIN(IF(AND($B2:$B28>=$O$22,$C2:$C28>=$O$52),$A2:$A28,0)*$A2:$A28,0),0)
G2:G8	G2	=FILTER(A2:A28,($B2:$B28>=$O$22)*($C2:$C28>=$O$52))
F2:F28	F2	=AND($B2>=$O$22,$C2>=$O$52)
Dynamic array formulas.

And what are you trying to do with this part of the formula:
=ROUND(MIN(IF(AND(Jan!$B:$B>=$O$22,Jan!$C:$C>=$O$52),Jan!$A:$A,0)*Jan!$A:$A,0),0)

And as you can see, FILTER can get you the results that meet both conditions, then apply the MIN() function to those results and you should be fine.

Book2
	G	H
2	19	19
3	22
4	23
5	24
6	25
7	26
8	27
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=FILTER(A2:A28,($B2:$B28>=$O$22)*($C2:$C28>=$O$52))
H2	H2	=MIN(FILTER(A2:A28,($B2:$B28>=$O$22)*($C2:$C28>=$O$52)))
Dynamic array formulas.

 

[Darren_workforce]

And what are you trying to do with this part of the formula:
=ROUND(MIN(IF(AND(Jan!$B:$B>=$O$22,Jan!$C:$C>=$O$52),Jan!$A:$A,0)*Jan!$A:$A,0),0)

I was just trying to get the basic formula to result in something other than an error so that was added purely to try and produce a result but can be ignored.

If the value in O52 is 52, that is Row 23 which matches up to Col A=22. If the value in O22 is 97%, that generates a result in Row 20 (Col A=19). If the 2 outcomes, since 22 is higher, the resulting value from Column A should be 22 and not 19 (apologies for mistyping 23 in the initial request). That's what I'm trying to come up with. Does that make sense?

 

[dreid1011]

And what are you trying to do with this part of the formula:
=ROUND(MIN(IF(AND(Jan!$B:$B>=$O$22,Jan!$C:$C>=$O$52),Jan!$A:$A,0)*Jan!$A:$A,0),0)

I was just trying to get the basic formula to result in something other than an error so that was added purely to try and produce a result but can be ignored.

If the value in O52 is 52, that is Row 23 which matches up to Col A=22. If the value in O22 is 97%, that generates a result in Row 20 (Col A=19). If the 2 outcomes, since 22 is higher, the resulting value from Column A should be 22 and not 19 (apologies for mistyping 23 in the initial request). That's what I'm trying to come up with. Does that make sense?

Yes it makes sense, and I realize I mistyped a value in col C. When sample data is posted in the table format you used, I have to manually re-enter it in excel because it copies as text and doesn't recognize numbers. So, the formula in H2 should be what you are after.

Book2
	A	B	C	D	E	F	G	H
1	Agents	SVL	Shrink
2	1	0.093207	31		0	FALSE	22	22
3	2	0.257581	32			FALSE	23
4	3	0.392158	33			FALSE	24
5	4	0.502341	34			FALSE	25
6	5	0.592551	35			FALSE	26
7	6	0.666409	36			FALSE	27
8	7	0.726879	37			FALSE
9	8	0.776387	38			FALSE
10	9	0.816921	39			FALSE
11	10	0.850108	40			FALSE
12	11	0.877279	41			FALSE
13	12	0.899524	42			FALSE
14	13	0.917737	43			FALSE
15	14	0.932649	44			FALSE
16	15	0.944858	45			FALSE
17	16	0.954853	46			FALSE
18	17	0.963037	47			FALSE
19	18	0.969737	48			FALSE
20	19	0.975223	49			FALSE
21	20	0.979714	50			FALSE
22	21	0.983391	51			FALSE
23	22	0.986402	52			TRUE
24	23	0.988867	53			TRUE
25	24	0.990885	54			TRUE
26	25	0.992537	55			TRUE
27	26	0.99389	56			TRUE
28	27	0.994998	57			TRUE
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROUND(MIN(IF(AND($B2:$B28>=$O$22,$C2:$C28>=$O$52),$A2:$A28,0)*$A2:$A28,0),0)
G2:G7	G2	=FILTER(A2:A28,($B2:$B28>=$O$22)*($C2:$C28>=$O$52))
H2	H2	=MIN(FILTER(A2:A28,($B2:$B28>=$O$22)*($C2:$C28>=$O$52)))
F2:F28	F2	=AND($B2>=$O$22,$C2>=$O$52)
Dynamic array formulas.

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G
1	Agents	SVL	Shrink		SVL	Shrink	Agents
2	1	0.093207	31		97%	52	22
3	2	0.257581	32				22
4	3	0.392158	33
5	4	0.502341	34
6	5	0.592551	35
7	6	0.666409	36
8	7	0.726879	37
9	8	0.776387	38
10	9	0.816921	39
11	10	0.850108	40
12	11	0.877279	41
13	12	0.899524	42
14	13	0.917737	43
15	14	0.932649	44
16	15	0.944858	45
17	16	0.954853	46
18	17	0.963037	47
19	18	0.969737	48
20	19	0.975223	49
21	20	0.979714	50
22	21	0.983391	51
23	22	0.986402	52
24	23	0.988867	53
25	24	0.990885	54
26	25	0.992537	55
27	26	0.99389	56
28	27	0.994998	57
Sheet1

Cell Formulas
Range	Formula
G2	G2	=INDEX(A2:A28,MATCH(1,(B2:B28>=E2)*(C2:C28>=F2),0))
G3	G3	=INDEX(FILTER(A2:A28,(B2:B28>=E2)*(C2:C28>=F2)),1)

 

[Darren_workforce]

Yes it makes sense, and I realize I mistyped a value in col C. When sample data is posted in the table format you used, I have to manually re-enter it in excel because it copies as text and doesn't recognize numbers. So, the formula in H2 should be what you are after.

Book2
	A	B	C	D	E	F	G	H
1	Agents	SVL	Shrink
2	1	0.093207	31		0	FALSE	22	22
3	2	0.257581	32			FALSE	23
4	3	0.392158	33			FALSE	24
5	4	0.502341	34			FALSE	25
6	5	0.592551	35			FALSE	26
7	6	0.666409	36			FALSE	27
8	7	0.726879	37			FALSE
9	8	0.776387	38			FALSE
10	9	0.816921	39			FALSE
11	10	0.850108	40			FALSE
12	11	0.877279	41			FALSE
13	12	0.899524	42			FALSE
14	13	0.917737	43			FALSE
15	14	0.932649	44			FALSE
16	15	0.944858	45			FALSE
17	16	0.954853	46			FALSE
18	17	0.963037	47			FALSE
19	18	0.969737	48			FALSE
20	19	0.975223	49			FALSE
21	20	0.979714	50			FALSE
22	21	0.983391	51			FALSE
23	22	0.986402	52			TRUE
24	23	0.988867	53			TRUE
25	24	0.990885	54			TRUE
26	25	0.992537	55			TRUE
27	26	0.99389	56			TRUE
28	27	0.994998	57			TRUE
Sheet1

Cell Formulas
Range	Formula
E2	E2	=ROUND(MIN(IF(AND($B2:$B28>=$O$22,$C2:$C28>=$O$52),$A2:$A28,0)*$A2:$A28,0),0)
G2:G7	G2	=FILTER(A2:A28,($B2:$B28>=$O$22)*($C2:$C28>=$O$52))
H2	H2	=MIN(FILTER(A2:A28,($B2:$B28>=$O$22)*($C2:$C28>=$O$52)))
F2:F28	F2	=AND($B2>=$O$22,$C2>=$O$52)
Dynamic array formulas.

That took care of it. Thank you!!

 

[dreid1011]

That took care of it. Thank you!!

You're welcome, and happy to help.