Got a toughy Dreamboat...

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

drdrew1469

Thread Starter
Joined
Nov 30, 2000
Messages
436
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

Anne
Joined
Feb 14, 1999
Messages
11,749
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

Thread Starter
Joined
Nov 30, 2000
Messages
436
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

Anne
Joined
Feb 14, 1999
Messages
11,749
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

Thread Starter
Joined
Nov 30, 2000
Messages
436
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

Thread Starter
Joined
Nov 30, 2000
Messages
436
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

Thread Starter
Joined
Nov 30, 2000
Messages
436
change the "h" formula to "VLOOKUP" and it will let you calc sizes up to 60" x 60"

Drew
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top