1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Got a toughy Dreamboat...

Discussion in 'Business Applications' started by drdrew1469, Nov 20, 2001.

Thread Status:
Not open for further replies.
Advertisement
  1. drdrew1469

    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
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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.
     
  3. drdrew1469

    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
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    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.)
     
  5. drdrew1469

    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
     
  6. drdrew1469

    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
     
  7. drdrew1469

    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
     
  8. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/59121

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice