converting fractions to decimal

[Guest]

As a data entry tool in an Excel form, I'd like to convert a numeric fraction
("3/4") contained in a "source cell" to its decimal equivalent ("0.75")
contained in a corresponding "target cell". The target cell needs to
dynamically react to any changes in the source cell (using formulas and
text-to-column delivers the correct result, but it is not dynamic). Is this
possible? How?

 

[Jim Cone]

It appears, after a little experimenting ,that
entering the fraction as a fraction: 0 3/4
or
number formatting the fraction as a fraction
allows the dependent cell to display decimals,
if the dependent cell is formatted as a number.

Jim Cone
San Francisco, USA


As a data entry tool in an Excel form, I'd like to convert a numeric fraction
("3/4") contained in a "source cell" to its decimal equivalent ("0.75")
contained in a corresponding "target cell". The target cell needs to
dynamically react to any changes in the source cell (using formulas and
text-to-column delivers the correct result, but it is not dynamic). Is this
possible? How?

 

[Biff]

Hi!

Assume A1 holds the fractional value. Cell A1 would have to be formatted as
TEXT, otherwise Excel will interpret 3/4 as a date.

=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,255)

Biff

 

[Guest]

Very,very cool! I'll spend a few minutes dissecting this expression (to
completely understand it), but you have to admire the result! Many thanks!

 

[Guest]

OK, here's the next layer of complexity (for me)...given that the resulting
decimal value in the target cell is used in other formulas, a valid number is
required in this target cell as to preclude an error condition in the
dependent formulas. When the source cell (containing the original fraction)
is blank, this otherwise cool fraction-to-decimal formula delivers a
non-numeric value in the target cell ("#VALUE!"). How can I condition the
target cell to defaut to a zero value in this instance?

 

[Biff]

Hi!

Try one of these:

=IF(A1="",0,LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,255))

=IF(ISERROR(LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,255)),0,LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,255))

Biff