less than greater than for large amount of data

E

excelnoob123456

New Member
Joined
Mar 7, 2015
Messages
39
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Type[/TD]
[TD]SizeA[/TD]
[TD]SizeB[/TD]
[TD][/TD]
[TD]222 (sizeA)[/TD]
[TD]222 (sizeB)[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]555 (size a)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]555 (size b)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]222[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]1436[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2725[/TD]
[TD][/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]2000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]1200[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]234[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]1303[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1984[/TD]
[TD][/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]2000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2100[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]1300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1700[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]607[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]1237[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1953[/TD]
[TD][/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]2000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2550[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]1500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1900[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64, align: right"]555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]1224[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2075[/TD]
[TD][/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]2550[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 70"]
<tbody>[TR="class: grid"]
[TD="width: 70"]2550[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2100[/TD]
[TD][/TD]
[TD]1600[/TD]
[TD]1300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

Code: 
=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.
 
Last edited:
Sort by date Sort by votes
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?
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
excelnoob123456 said:
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?
Click to expand...

Hi Excelnoob123456,

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

Markmzz
​
 
Last edited:
Upvote 0
excelnoob123456 said:
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?
Click to expand...

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.
 
Upvote 0
markmzz said:
Hi Excelnoob123456,

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

Markmzz
​
Click to expand...

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
 
Upvote 0
markmzz said:
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
Click to expand...

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 .
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Help with Nested "If greater than, but less than" statement
Replies
2
Views
2K
awoohaw
awoohaw
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
186
Omran Y
O
J
  • Question Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
413
jmk15315
J
O
  • Solved
Put amounts for adjacent items based on matching column between two sheets
Replies
3
Views
74
dreid1011
dreid1011
L
  • Solved
COUNTIF? Would like to count text over a range of cells and if greater than or equal to 2 lots of text "" or if less than 2 lots of text ""
Replies
3
Views
575
Shesil
Shesil

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top