less than greater than for large amount of data

[excelnoob123456]

Hi Guys

I am OK with Excel and I know a way that I can do this, using If and AND formulae, but I feel there must be a neater way to do this given the large amount of data I'm working with would make my formula a bit unwieldy.

See example data below:

	A	B	C	E	F	G	H	I	J
1	Type	SizeA	SizeB		222 (sizeA)	222 (sizeB)		555 (size a) <tbody> </tbody>	555 (size b)
2	222 <tbody> </tbody>	1436 <tbody> </tbody>	2725		2000 <tbody> </tbody>	1500		1200 <tbody> </tbody>	1500
3	234 <tbody> </tbody>	1303 <tbody> </tbody>	1984		2000 <tbody> </tbody>	2100		1300 <tbody> </tbody>	1700
4	607 <tbody> </tbody>	1237 <tbody> </tbody>	1953		2000 <tbody> </tbody>	2550		1500 <tbody> </tbody>	1900
5	555 <tbody> </tbody>	1224 <tbody> </tbody>	2075		2550 <tbody> </tbody>	1500		1500	1200
6					2550 <tbody> </tbody>	2100		1600	1300

<tbody>
</tbody>

Now, in my data all of the types (eg. 222,234,607,etc) have their own sizes as shown, but I want to return the 'allowable size' from the tables to the right, all of the types have their own different allowable sizes and sometimes the values they have can be outside of the allowable range, in which case I would like to display "width too large" for example. Now, in reality the SizeA and B are both in the same cell eg. 1224x2075 for both sets of data, and I think I will add columns where I seperate this out to make the formula easier. So currently my thinking would be to do something like:

=IF(and(A2=F1,b2<=f2,c2<=g2),concatenate(f2&"x"&g2),if(and(a2=f1,b2<=f2,c2>g2,c2<=g3),concatenate(f2&"x"&g3),etc.(

Going through the who line of options this would be quite a cumbersome formula, I think there must be an easier way to do this but I'm not sure how?

Any help greatly appreciated.

 

[excelnoob123456]

Excellent! Thanks very much Stephen! Yes, the problem was that I wasn't inputting them as ranges. I have experience of index match but only really as a substitute for vlookups, I'm still trying to work out how this actually works! Specifically, why the numbers are multiplied? I assume you are comparing the areas to find smallest allowable?

 

[markmzz]

Excellent! Thanks very much Stephen! Yes, the problem was that I wasn't inputting them as ranges. I have experience of index match but only really as a substitute for vlookups, I'm still trying to work out how this actually works! Specifically, why the numbers are multiplied? I assume you are comparing the areas to find smallest allowable?

Hi Excelnoob123456,

Did you try my suggestions of posts #9 and #10?

Markmzz
​

 

[StephenCrump]

I'm still trying to work out how this actually works! Specifically, why the numbers are multiplied? I assume you are comparing the areas to find smallest allowable?

Correct! Here are the steps in the formula for D3, using the layout in Post #4 and the first example: Type 222: 1436x2050

=(G3:G7>=B3)*(H3:H7>=C3)
={0;1;1;0;1}
i.e. the 2nd, 3rd and 5th sizes will work.

But what about turning the wood the other way?
=((G3:G7>=B3)*(H3:H7>=C3))+((G3:G7>=C3)*(H3:H7>=B3))
={0;1;1;1;2}
i.e the 5th size works both ways, only the first is too small.

What are the areas?
=IF(((G3:G7>=B3)*(H3:H7>=C3))+((G3:G7>=C3)*(H3:H7>=B3)),G3:G7*H3:H7)
={FALSE;4200000;5100000;3825000;5355000}

What is the minimum area?
{=MIN(IF(((G3:G7>=B3)*(H3:H7>=C3))+((G3:G7>=C3)*(H3:H7>=B3)),G3:G7*H3:H7))}
=3825000

Where does this minimum area happen?
{=MATCH(MIN(IF(((G3:G7>=B3)*(H3:H7>=C3))+((G3:G7>=C3)*(H3:H7>=B3)),G3:G7*H3:H7)),IF(((G3:G7>=B3)*(H3:H7>=C3))+((G3:G7>=C3)*(H3:H7>=B3)),G3:G7*H3:H7),)}
=4, i.e in the fourth row, for size 2550x1500.

However, in practice we won't always want to use G3:G7. Instead we'll need:
=INDEX(Sizes,,MATCH(A3,Headers,))
={2000;2000;2000;2550;2550} in this case
But if A3 is 607, say, will be {1200;1300;1500;1500;1600}

So replace all G3:G7 in our formula with INDEX(Sizes,,MATCH(A3,Headers,))
and replace all H3:H7 with 1+INDEX(Sizes,,MATCH(A3,Headers,))

In practice, I'd break a formula like this down into its components to make it easier to understand and debug. Markmzz has suggested a couple of alternative ways you could do this.

 

[markmzz]

Hi Excelnoob123456,

Did you try my suggestions of posts #9 and #10?

Markmzz
​

Hello!

If your goal is to look at both sides SizeAxSizeB and SizeBxSizeA, then the formula of the name below can help you (in addition to the others of my post #10):

ColAxColB - Refers to:

=IF((INDEX(SizeA,ROW()-ROW(INDEX(SizeA,1))+1)<=DColSA)*(INDEX(SizeB,ROW()-ROW(INDEX(SizeB,1))+1)<=DColSB)+
(INDEX(SizeB,ROW()-ROW(INDEX(SizeB,1))+1)<=DColSA)*(INDEX(SizeA,ROW()-ROW(INDEX(SizeA,1))+1)<=DColSB),DColSA*DColSB)

Markmzz

 

[excelnoob123456]

Hello!

If your goal is to look at both sides SizeAxSizeB and SizeBxSizeA, then the formula of the name below can help you (in addition to the others of my post #10):

ColAxColB - Refers to:

=IF((INDEX(SizeA,ROW()-ROW(INDEX(SizeA,1))+1)<=DColSA)*(INDEX(SizeB,ROW()-ROW(INDEX(SizeB,1))+1)<=DColSB)+
(INDEX(SizeB,ROW()-ROW(INDEX(SizeB,1))+1)<=DColSA)*(INDEX(SizeA,ROW()-ROW(INDEX(SizeA,1))+1)<=DColSB),DColSA*DColSB)

Markmzz

Hi Markmzz

Thanks very much for this, sorry I did have time to reply the last few days.

Thanks very much Stephen also for explaining the formula, I think I am starting to understand how it works now.

Markmzz I will have a go at working out yours as well as it does seem like a 'neater' formula.

I actually do have another issue at the moment, which is adding another variable into the mix, but I think I will do that just by combining the name with the other name type .