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
=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