Apr 27 2019 09:53 AM
Enclosed is my spreadsheet to total add all feet, inches and fractions. |
Then convert them all back to Feet, Inches and Fractions |
The table VLOOKUP function looks at the value in cell F14 "faction" |
The data table in M4 to N195 is named "RANGE" |
This table represents all inch and fractions from 1/16" to 11 15/16" IN DECIMALS OF A FOOT! |
When you add or subtract a fraction that =0 I get an error in cell F15 |
When you add or subtract a fraction that is >0 I get an error in cell F18 |
I have been trying to correct the error for way too long |
This is why I am reaching out to someone to PLEASE assist me with this error! |
THANK YOU! |
Apr 27 2019 10:50 AM
Your VLOOKUP is incorrect here
=IF(FRACTION=0,"0",VLOOKUP(RANGE,2,))
if change the syntax on
=IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2))
it works
Apr 27 2019 11:21 AM
Apr 27 2019 12:02 PM
Thanks but i have never used that function.
If possible could please give me an example as it pertains to my spreadsheet.
Thank You!
Apr 27 2019 12:11 PM
Thank you for the formula, I appreciate any help.
I still have 1 problem (sorry)
The vlookup function always picks a row above the stated lookup value.
IF if i input a fraction into column F example, input 1/2" it always returns one row above as 7/16"
And yes i tried change to FALSE for an exact match and get a #N/A.
Sorry but i have been working on this for a very long time, on & off.
I sure would like to put this to bed.
Thanks again
Apr 27 2019 12:34 PM
It depends on what you'd like to receive. With aproximate search you have closest value before searched one. With exact search you may wrap your formula with IFNA and return any value you'd like for such case, e.g.
=IFNA(<your formula>, 0)
Apr 27 2019 12:44 PM
Oops, the issue is another - your range is calculated and you have rounding error, thus no exact match. As variant you may correct the formula to
=VLOOKUP(ROUND(FRACTION,6),ROUND(RANGE,6),2)
(see in J15)
Apr 27 2019 01:30 PM
See attached file.
Using CONVERT() also means you can get rid of your helper table and your rounding problem.
Apr 27 2019 02:55 PM
VERY close but no cigar.
Tried the example as above & get a #VALUE! error.
The previous formula works awesome, =VLOOKUP(FRACTION,RANGE,2,TRUE)
EXCEPT
The only error i get if my input area is completely BLANK. then i get a #N/A
If there is only 1 piece of data input it works. Range from C5 to F12.
Not a big game changer, just a small annoyance. Otherwise all is correct.
I do want to THANK YOU VERY MUCH for you taking the time to help me with this problem.
I really appreciate it
THANK YOU!
Apr 27 2019 03:05 PM
SolutionI STAND CORRECTED! Used the formula
=IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2)) |
WORKS PERFECT !!!
Finally thanks to your help!
Apr 27 2019 03:05 PM
SolutionI STAND CORRECTED! Used the formula
=IF(FRACTION=0,"0",VLOOKUP(FRACTION,RANGE,2)) |
WORKS PERFECT !!!
Finally thanks to your help!