Forum Discussion
Solved
IF/THEN and WILDCARD assistance
Good afternoon y'all,
This will be a two-part question because my excel sheet has two areas that I need assistance with.
Part 1
I have created an excel sheet that helps me calculate needed material amounts and labors regarding flooring installations. I need for some of the calculations to display answers that are in increments of inches in their decimal form (i.e. 1" = 0.08 / 2" = 0.16 / 9" = 0.75/ etc....) My table accurately does this for most of the calculations aside from three of the inch increments. Whenever the table calculates for 2", 5", and 8" it gives the answers 0.17, 0.42, and 0.67 respectively. I need help correcting my formula so that whenever excel notices the endings to say any of these three examples (0.17, 0.42, and 0.67) that it will then correct the answer to say 0.16, 0.41, or 0.66.
My current formula is as follows =ROUND(CEILING(B10*B3, 1/12),2)
If I add =ROUNDUP..... or =ROUNDDOWN... then many more of the numbers will be off. By simply putting =ROUND... it more accurately gives me the answers that I need except for those three outliers that I just described.
Part 2
As I have said before the excel sheet in question is to help with calculating needed material amount and labors associated with flooring installations. The next issue has to do with calculating the amount of trim pieces needed. I am hoping that this second issue will be a little easier to solve.
I have written a formula that will take the linear footage of product needed, divide it by the product's length, and then round that number up to a whole number. I will provide a few examples to show what it currently does:
Ex. 1
96" T-mold = 8' T-mold
24 LF needed according to a separate program
The formula will say that I need 3 T-molds.
(24 / 8 = 3)
Ex. 2
94" T-mold = 7.83' T-mold
24 LF needed according to a separate program
The formula will calculate 3.06 and then round up to 4 T-molds needed.
Ex. 3
94" T-mold = 7.83' T-mold
39 LF needed according to a separate program
The formula will calculate 4.98 and then round up to 5 T-molds needed.
The issue lies within the third example. If the program calculates a number ending between 0.75 and 0.99 I want it to add one additional stick. So for Ex. 3 above, I would prefer it say that 6 T-molds are needed instead of 5. The current formula I am using to calculate these trim pieces is as follows =ROUNDUP(F25/ROUNDDOWN(D25/12,2),0).
Any and all assistance given is greatly appreciated. Please let me know if you need any more examples or further explanation to help solve my issues.
LORDPHILLIP Alternatively, and if you want to simplify it a bit enter this in D7:
=LET( a,C7/(A7/12), b,--(MOD(a,1)>=0.75), ROUNDUP(a,0)+b)and copy down.
Same principle but no need for an IF inside LET.
Since you have a spreadsheet already, just need help with it, you could help us help you by posting the spreadsheet itself as well as your questions. If you can't attach it to a message here, put it on OneDrive or GoogleDrive with a link pasted here that grants access to it. This is assuming that there's nothing particularly proprietary or confidential in it. If there is, create an excerpt that demonstrates the problems you're asking about.
I have attached the excel doc as requested. Again, thank you for any and all time taken to help me with my issues.
I want to give you an update though, I received some help from another source for the first issue on this posting. When you open the excel doc and put your cursor in any of the three "GROSS" calculation boxes you will notice that there is a very long formula that fixes the issue I was having with rounding each response up to the nearest inch.
I would still like some help with the second issue about the trim section. Please note that the two spots saying "TRIM 1" and "TRIM 2" are the areas of concern, not the section just below it saying "RISERS".
Please let me know if you have any questions or concerns!
Because looking at this spreadsheet is like looking at a different language--it's material/subject matter I have no familiarity with, I don't really get what you're saying. Maybe if you entered, beside the current formula's results, the result that you desire, with a specific explanation for each line. Use an inserted text box to explain.
Right now, because of the ROUNDUP and ROUNDDOWN you're getting whole numbers, so that your examples with various decimal figures don't make sense. But I can't even follow where I'd be entering the raw data to replicate what you're describing. You are very familiar with all the components on this page; so it seems obvious to you, as is understandable. But set it up more clearly for the folks who know Excel but don't know a T-Mold from a T-Bone.
