How to use a part of a fraction (converted from a decimal) in another formula?

[elblanco]

I have a decimal number in cell A1. Cell A1 is converted to A2 (=A1) which is formatted as a fraction in eighths and is displaying 7/8. I want to extract only the 7 and use it in another formula.
When I execute cell A3 which is: =LEFT(TEXT(A2,"?/?"),1) I get a 6. I never get the correct number with any of the fractions. I'm stumped... can anyone help please. I'm using Excel 2010. Thanks.

 

[jasonb75]

Sound like the value in A1 is closer to 6/7 than 7/8. Using =A1 in A2 then formatting it as ?/8 doesn't change the underlying decimal value, formats only change how things appear. Any subsequent formulas will still use the original value unless it is modified by a formula (not a format).

=LEFT(TEXT(A1,"?/8"),1)

Should do what you need, as long as the decimal value is always less than 1.

 

[elblanco]

Sound like the value in A1 is closer to 6/7 than 7/8. Using =A1 in A2 then formatting it as ?/8 doesn't change the underlying decimal value, formats only change how things appear. Any subsequent formulas will still use the original value unless it is modified by a formula (not a format).

=LEFT(TEXT(A1,"?/8"),1)

Should do what you need, as long as the decimal value is always less than 1.

Jason, you're the best. Thank you so much -- that did it!
Stay safe.

 

[elblanco]

Jason, you're the best. Thank you so much -- that did it!
Stay safe.

 

[elblanco]

Also Jason, I had to multiply the extracted character by 1 to put it in a numeric mode that would work in the formula. Just what you referred to above in your explanation.
So thanks again.

 

[Rick Rothstein]

You could also use this formula as well (it returns a numerical value directly)...

=ROUND(8*A1,0)

 

[elblanco]

Thanks Rick, that's even better.