Got a toughy Dreamboat...

[drdrew1469]

Here we go... in a cell A1 is put a size width in inches "18". In cell A2 is a HLOOKUP function that checks cell A1, goes to a chart and returns a value based on "18" (width). In cell B1 is put a size height in inches "36". In cell B2 is a VLOOKUP function that checks cell B1, goes to the same and returns a value based on "36" (height). I then have an INDEX function in cell C1 that uses the return values from cells A2 and B2 to return the "intersecting" answer of 18" x 36". My problem is that when I put in an odd size (16" x 30") I can't get the LOOKUP functions to round the answer up to the next size because the chart goes in 6" increments. It rounds down to the next lower answer by default which throws my "intersecting" answer off. It works, but I am trying to stay away from this:

=IF(AND(A1>30,A1<=36),5,IF(AND(A1>24,A1<=30),4,IF(AND(A1>18,A1<=24),3,IF(AND(A1>12,A1<=18),2,IF(A1=12,1,0))))

because my chart is huge and would take 30 some cells of "IF(AND" statements. I tried ROUNDUP within the VLOOKUP and HLOOKUP to no avail. Maybe I wrote it wrong. I tried MATCH with a -1 and that didn't work either. Maybe I wrote that one wrong too...

Please help me...I'll be your best friend,

Drew

 

[Anne Troy]

I'm at work with a limited attention span at the moment, but try:

=roundup(vlookup....)

If you reply later, I'll be able to really check it out. Heading home now.

 

[drdrew1469]

Dreamboat,

Tried it and cried it. It didn't work. Maybe I should send it to you...yeah, that's the ticket! send it to you (and you'll see I'm not crazy. "yes you are". who said that.

Sorry, I get on those tangents every now and then. I even tried installing the option pack to use MROUND (well, it did install... but the function didn't work anyway, unless I wrote that wrong too.) I'm at my wits end on this (which are very little, obviously.)

Please advise,

Drew

 

[Anne Troy]

Email it: [email protected]

(After describing this to someone else in the parking lot on the way home, I realized the round wouldn't do it...doh! THAT person is also being "paged" into your question here.)

 

[drdrew1469]

Dreamboat, I just sent it to you. You can reply to me at that address or repost if you have any questions. I'll be on all day.

Thanks,

Drew

 

[drdrew1469]

Dreamboat,

I found that I can do this to (which is much better than =if(and's in 20 different cells), but isn't quite what I though Excel could do. This only allows 30 "or's" per cell so I'm down to referencing much less than =if(and. Try this on for size in the sheet I sent you.

"w":
=IF(OR(M3=12,M3=16,M3=18,M3=24,M3=30,M3=36,M3=42,M3=48,M3=54,M3=60),HLOOKUP(M3,AF3:AO4,2),HLOOKUP(M3,AF3:AO4,2)+1)

"h":
=IF(OR(Q3=12,Q3=16,Q3=18,Q3=24,Q3=30,Q3=36,Q3=42,Q3=48,Q3=54,Q3=60,Q3=66,Q3=72,Q3=78,Q3=84,Q3=90,Q3=96),HLOOKUP(Q3,AF3:AO4,2),HLOOKUP(Q3,AF3:AO4,2)+1)

and the "index" is now:
=IF(OR(B3=0,M3=0,Q3=0),"",INDEX(AG5:AO19,Y8,Y6))

Thanks,

Drew

 

[drdrew1469]

change the "h" formula to "VLOOKUP" and it will let you calc sizes up to 60" x 60"

Drew