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 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.
I had to do some research to understand how that double negative works in that sexy formula you got from Riny_van_Eekelen If you understood it, you're a better man than I... but if you were happy that it worked but wondered how it did, here's the answer.
Here's one of the better explanations. You can also find some videos on YouTube by searching for "double negatives in excel"
- FANTASTIC! Thank you so much Riny van Eekelen! And thank you to everyone else as well for taking the time to help me!
I was lying in bed this morning and it suddenly hit me that I'd made a careless mistake in my last formula. I know, I know: what healthy person lies in bed thinking of Excel formulas??!!
Anyway, now that I have come in to correct that, I see that my on-line (never met in person) colleagues have resolved it for you already.
OliverScheurich corrected my stupid error.
Riny_van_Eekelen did what I was sure somebody else would do, and that was to come up with an elegantly simple formula.
Here, for the sake of your understanding of the formula that OliverScheurich and I used, is the correction made explicit
=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,1), creates an adder (Addr) of 2 if Rmndr >=.75, otherwise 1**
INT(FstPass)+Addr final formula just adds Addr to INT(FstPass)
)
But I appreciate the elegance of the formula created by Riny_van_Eekelen and suggest you mark it as "Best Solution"
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.
=LET(Sz2Ft,ROUND(A2/12,2),
FstPass,C2/Sz2Ft,
Rmndr,FstPass-INT(FstPass),
Addr,IF(Rmndr=0,0,IF(Rmndr>=0.75,2,1)),INT(FstPass)+Addr)
I've made slight modification to mathetes formula and it returns the intended result in my understanding.
For 94 SIZE IN INCH and 452 INPUT LF the correct answer should be 58 in my understanding.
94 / 12 = 7,83
452 / 7,83 = 57,72669
0,72669 is not between 0,75 and 0,99 therefore result should be 58.
