Forum Discussion
IF/THEN and WILDCARD assistance
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.
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.
- I sent a reply last night, but I think I may have accidentally replied to myself instead of you.
- Thank you for your feedback. I will definitely put some more context and notes on the spreadsheet to help clarify what information is being displayed.
I have attached an updated spreadsheet with the requested information.
Please let me know if you have any questions or concerns.
I came up with a formula that gets three of the four lines correctly. I don't know what's different about the third of the four, because it ends up with a result different from the desired. You'll need to let me know what's different about the relationships there between the two named variables.
There may well be more elegant formulas, but I like the way the LET function works to create a more readable formula. It does that by making it possible to name variables for use in the formula, and then at the end, to write a simple formula. Like this
=LET( just begins the function
Sz2Ft,A7/12, defines Sz2Ft as A7 divided by 12
FstPass,C7/Sz2Ft, defines FstPass as C7 divided by Sz2Ft
Rmndr,FstPass-INT(FstPass), defines Rmndr, the decimal portion of FstPass*
Addr,IF(Rmndr>=0.75,2,0), creates an adder (Addr) of 2 if Rmndr >=.75
INT(FstPass)+Addr final formula just adds Addr to INT(FstPass)
)
*Rmndr is determined by subtracting the Integer value of FstPass from the full value of FstPass
As noted, it probably could be made more complex, and compact, but it would then lose readability.
But you'll need to adapt it for that third line, or tell me what's different about the components there.