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.
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 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.
I've made some changes to the spreadsheet and attached the new version. I hope my changes can show a little more clearly what I am asking for, and I am sorry if it seems confusing.
You spoke correctly earlier saying that most of the information on these tables is clear and evident to me because I look at these numbers on the regular, and it becomes a little difficult to remember what needs more clarification.Thank you so much for sticking with me on this. This is the final part I need help with in order to make this whole project usable and ready for the field. Again, please let me know if you have any questions or concerns, and/or need more clarification. I'm willing to break down any and all details to its lowest form if needed.