Equal to or Less than, but Greater than question

[JCrain]

I have a quite complex list of variables to put into a formula to come up with the correct number and I need some help to figure this one out. I don't know if it would be easier to set up a chart and then index the chart to do a lookup or how I need to go about doing this to achieve the most efficent solution.

I need the following to go into one formula:

1. =< 24 needs to show the number 24
2. =< 30 but > 24 needs to show 30
3. =< 32 but > 30 needs to show 32
4. =< 36 but > 32 needs to show 36
5. =< 40 but > 36 needs to show 40
6. =< 48 but > 40 needs to show 48
7. =< 50 but > 48 needs to show 50
8. =< 60 but > 50 needs to show 60
9. => 60 needs to show 60

I am trying to set up a spread sheet to figure square footage of plastic laminate countertops, but the plastic laminate only comes in several stock sizes : 3x8, 4x8, 5x8
3x10, 4x10, 5x10
3x12, 4x12, 5x12

So if I have a countertop that is 25 sq ft., I cant use a 3x8 (24 sq. ft), so I need to go up to the next size which would be a 4 x 8.

I might need to use a ROUNDUP function with this formula too? I already have cells set up to figure the depth, backsplash height, and front edge height and then give me the squage footage of that.

I am attaching a copy of what I have created so far if someone needs to see an example of what I am trying to do.

Thank you for the help.

[darkyam]

If 0,24,30,32,36,40,48,50,60 are in A1:A9 and the value to lookup is in B1, =VLOOKUP(B1,A$1:A$9,1,True)

[JCrain]

If 0,24,30,32,36,40,48,50,60 are in A1:A9 and the value to lookup is in B1, =VLOOKUP(B1,A$1:A$9,1,True)

This will work to lookup the number I need, but if I have a number that is between 2 of the numbers on the chart, it does not round up. Example: If the total square footage is 25.5 feet, I will need it to round up to 30, not stay at 24. How and where would I add a ROUNDUP function to this formula to make it work correctly?

[NBVC]

I have a quite complex list of variables to put into a formula to come up with the correct number and I need some help to figure this one out. I don't know if it would be easier to set up a chart and then index the chart to do a lookup or how I need to go about doing this to achieve the most efficent solution.

I need the following to go into one formula:

1. =< 24 needs to show the number 24
2. =< 30 but > 24 needs to show 30
3. =< 32 but > 30 needs to show 32
4. =< 36 but > 32 needs to show 36
5. =< 40 but > 36 needs to show 40
6. =< 48 but > 40 needs to show 48
7. =< 50 but > 48 needs to show 50
8. =< 60 but > 50 needs to show 60
9. => 60 needs to show 60

I am trying to set up a spread sheet to figure square footage of plastic laminate countertops, but the plastic laminate only comes in several stock sizes : 3x8, 4x8, 5x8
3x10, 4x10, 5x10
3x12, 4x12, 5x12

So if I have a countertop that is 25 sq ft., I cant use a 3x8 (24 sq. ft), so I need to go up to the next size which would be a 4 x 8.

I might need to use a ROUNDUP function with this formula too? I already have cells set up to figure the depth, backsplash height, and front edge height and then give me the squage footage of that.

I am attaching a copy of what I have created so far if someone needs to see an example of what I am trying to do.

Thank you for the help.

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear around the formula

[Portuga]

This will work to lookup the number I need, but if I have a number that is between 2 of the numbers on the chart, it does not round up. Example: If the total square footage is 25.5 feet, I will need it to round up to 30, not stay at 24. How and where would I add a ROUNDUP function to this formula to make it work correctly?

In another sheet, you can list your values from 1 to 60 (Column a) Put the corresponding condition for all numbers Column B.

Apply the Vlookup formula to this 2 columns ("True" at the end - for values above 60)

[darkyam]

Sorry, JCrain, I apparently didn't read your requirements thoroughly.

[JCrain]

In another sheet, you can list your values from 1 to 60 (Column a) Put the corresponding condition for all numbers Column B.

Apply the Vlookup formula to this 2 columns ("True" at the end - for values above 60)

I am sorry, but I am still VERY new to using Excel and I am not sure I completely understand what you told me how to do. I am attaching a copy of what I set up, but I don't know if it is set up correctly or how to type up the LOOKUP function correctly. Could you possibly show me an example? I am sorry I do not understand, I am sure this is a simple function for someone who has been using Excel for awhile, but I havent used Excel since my sophomore year in college and that was about 10 years ago.

[NBVC]

Did you see/try my suggestion?

[JCrain]

Sorry, JCrain, I apparently didn't read your requirements thoroughly.

Thats ok. You did help me get started though. I didn't really have any idea how I was going to do what I need to do. I wish I could talk to the people on this forum without typing everything out because I think I would be able to explain things better. I am trying to use all the formulas I get help with off this forum to create a spreadsheet to bid jobs for our company. My boss does all his bids (and everything else) with pencil and paper and I am trying to convert all his calculations (and faxes, transmittals, etc) to the computer so everything will be neater and more efficient, ie: up to date with technology .

Thank you for your help with this problem and the previous posts I have submitted. I am a lot farther along with my work than I would have been without your help and suggestions.

[JCrain]

Did you see/try my suggestion?

I tried this formula and the cell records the number "24". I am not sure that I am putting this formula in the right spot, or maybe my chart is wrong.

Coulmn A1 has the numbers 0,24,30,32,36,40,48,50,60. Is this set up correctly?

I am not sure. I am open to any help or suggestions that anyone can give me, because I don't know how to do very much on Excel. If someone can tell me where to put a formula and what I need to go with that formula, I can figure out how to modify it if I need to most of the time.

[NBVC]

I tried this formula and the cell records the number "24". I am not sure that I am putting this formula in the right spot, or maybe my chart is wrong.

Coulmn A1 has the numbers 0,24,30,32,36,40,48,50,60. Is this set up correctly?

I am not sure. I am open to any help or suggestions that anyone can give me, because I don't know how to do very much on Excel. If someone can tell me where to put a formula and what I need to go with that formula, I can figure out how to modify it if I need to most of the time.

If your numbers are in A1:A9, try:

Please Login or Register  to view this content.

Remember, after you enter the formula you must hold the CTRL and SHIFT keys down before pressing ENTER.

[JCrain]

Remember, after you enter the formula you must hold the CTRL and SHIFT keys down before pressing ENTER.

I am being serious when I ask this, but what does this do?

I think it might help better explain what I am trying to do if I revise my original post:

I need a formula to do the following:

1. Cell A13 needs to be the Sum of E13+F13+G13.
2. If the number that is posted in cell A13 is =< 24, in needs to show "24"
3. If the number is =< 30, but > 24, it needs to show "30"
4. If the number is =< 32, but > 30, it needs to show "32"
5. If the number is =< 36, but > 32, it needs to show "36"
6. If the number is =< 40, but > 36, it needs to show "40"
7. If the number is =< 48, but > 40, it needs to show "48"
8. If the number is =< 50, but > 48, it needs to show "50"
9. If the number is =< 60, but > 50, it needs to show "60"
10. If the number is =< 84, but > 60, it needs to show "84"

Etc. ( I will have to figure out all the square footage combinations that we might use and then I can add those numbers to a chart. ) I think this would be enough to get me started so I know what the formula needs to contain.

I am sorry that I did not include in my first post that the number I am seeking will be a result of addition performed with numbers obtained from 3 different cells.

[NBVC]

See A13 in the attached.

Quote:
Originally Posted by NBVC
Remember, after you enter the formula you must hold the CTRL and SHIFT keys down before pressing ENTER.


I am being serious when I ask this, but what does this do?

It makes the formula an array formula so that Excel knows how to calculate the result... see here for more on Array formulas: http://www.cpearson.com/excel/ArrayFormulas.aspx

[JCrain]

See A13 in the attached.

Ok. Thank you. I think this will work now.

Please Login or Register  to view this content.

But how does this code know to index the numbers that are on a completely different worksheet?

Can I add numbers to the chart on the other worksheet and then add them to this code and it work correctly, or is there a limit on the number of variables that can be included in one array?

I am sorry that I am asking so many questions about everything, but I am really trying to teach myself how to understand and use Excel so maybe I will be able to help people in the future who are new users like I am.

[NBVC]

Ok. Thank you. I think this will work now.

Please Login or Register  to view this content.

But how does this code know to index the numbers that are on a completely different worksheet?

Can I add numbers to the chart on the other worksheet and then add them to this code and it work correctly, or is there a limit on the number of variables that can be included in one array?

I am sorry that I am asking so many questions about everything, but I am really trying to teach myself how to understand and use Excel so maybe I will be able to help people in the future who are new users like I am.

Actually, I didn't see that you had created a table in the PL Sizes sheet, so my formula "hard-coded" the index numbers... to use your table...try:

=INDEX('PL Sizes'!A1:A9,MATCH(TRUE,'PL Sizes'!A1:A9>=ROUNDUP(SUM(E13:G13),0),0))

again confirmed with CTRL+SHIFT+ENTER...

If you want to be able to make your list in PL Sizes dynamic so that you can add/subtract index numbers, then you have to create a defined named range.

Go to Insert|Name|Define and enter a name such as "PLSizes" (without quotes) in the Names in Workbook field. Then enter this formula in the Refers to field:

Please Login or Register  to view this content.

Now update your formula in A13 to:

Please Login or Register  to view this content.

Now if you add more items to the list in the PL Sizes sheet, they will be incorporated into the formula.

See attached.

[JCrain]

How in the world did you learn how to do things like this and make them work the way you want them to? Did you help write the Excel program or something?

I don't think I would be able to do half of what I have done without them help from people on this forum.