Hi, does anyone know how to get Excel to stop trying to interpreting my data?
I regularly need to input data in the fractional form (13/5, 12/24, 11/5119,
etc) that then needs to be presentable AND manipulatable. If I type in as
is, the number gets interpreted as a date; if I format as text, the data is
no longer capable of being plugged into functions; if I format as a fraction,
excel interprets and simplifies the fractions and only allows for three
figure denominators. Does anyone know how to get Excel to just trust me that
I know what I'm inputting?

Well, what are you inputting? What functions are you using that don't accept
the data you have?

The standard solution is to use the data as text. Why won't that work for
you?

Regards,
Fred

"Saven" wrote in message
...
Hi, does anyone know how to get Excel to stop trying to interpreting my
data?
I regularly need to input data in the fractional form (13/5, 12/24,
11/5119,
etc) that then needs to be presentable AND manipulatable. If I type in as
is, the number gets interpreted as a date; if I format as text, the data
is
no longer capable of being plugged into functions; if I format as a
fraction,
excel interprets and simplifies the fractions and only allows for three
figure denominators. Does anyone know how to get Excel to just trust me
that
I know what I'm inputting?

Most of the time I'm inputting field data. Other times, like now, I'm
analyzing enrollment statistics on a day by day basis. This requires
inputting data on a class in the format of (seats available)/(waitlist
available) where the two values are unrelated integers. This data needs to
be both displayable as-is (i.e. as 32/3) and and manipulatable with functions
such as "=(C19*3-D19*3)/(C19*3)". When you format a cell (or column) as text
and attempt to use it in an equation Excel will display "#VALUE!" as the
answer unless the equation is a simple integer arithmetic fxn or matches one
of its date/time models.

-Saven

What you are trying to do is not supported by Excel. Excel was designed to
do calculations. Anything with an operator (like "/"), it tries to resolve
as a mathematical equation. If it's not an equation, then it looks for a
date.

Most people would solve your problem by:
-- using separate cells for seats available, and waitlist available
-- Have another cell for display purposes, such as =a1&"/"&b1

In the example you gave, you are multiplying each cell by 3, which says to
me you are trying to offset the divisor in the source cell. You'd be better
off with the data in separate cells, then your formula becomes
(c19-d19)/c19.

Regards,
Fred

"Saven" wrote in message
...
Most of the time I'm inputting field data. Other times, like now, I'm
analyzing enrollment statistics on a day by day basis. This requires
inputting data on a class in the format of (seats available)/(waitlist
available) where the two values are unrelated integers. This data needs
to
be both displayable as-is (i.e. as 32/3) and and manipulatable with
functions
such as "=(C19*3-D19*3)/(C19*3)". When you format a cell (or column) as
text
and attempt to use it in an equation Excel will display "#VALUE!" as the
answer unless the equation is a simple integer arithmetic fxn or matches
one
of its date/time models.

-Saven

One way is to put a 0 and a space before entering the fraction like shown below
0 4/5
0 7/8
Note the space after the zero and before the fraction.

"Saven" wrote:

Hi, does anyone know how to get Excel to stop trying to interpreting my data?
I regularly need to input data in the fractional form (13/5, 12/24, 11/5119,
etc) that then needs to be presentable AND manipulatable. If I type in as
is, the number gets interpreted as a date; if I format as text, the data is
no longer capable of being plugged into functions; if I format as a fraction,
excel interprets and simplifies the fractions and only allows for three
figure denominators. Does anyone know how to get Excel to just trust me that
I know what I'm inputting?

Hi Sheeloo, I tried inputting "0 " in front of my data and it worked very
well for values less than 1, unfortunately for values such as 32/3, Excel
still simplified them to "10 2/3", but still, that will help for some of my
data. Thanks!

-Saven

prefix the entry with a single quote: ' 32/3

Excel will write this as a text entry.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Saven" wrote in message
...
Hi, does anyone know how to get Excel to stop trying to interpreting my
data?
I regularly need to input data in the fractional form (13/5, 12/24,
11/5119,
etc) that then needs to be presentable AND manipulatable. If I type in as
is, the number gets interpreted as a date; if I format as text, the data
is
no longer capable of being plugged into functions; if I format as a
fraction,
excel interprets and simplifies the fractions and only allows for three
figure denominators. Does anyone know how to get Excel to just trust me
that
I know what I'm inputting?

Saven wrote:
Hi, does anyone know how to get Excel to stop trying to interpreting my data?
I regularly need to input data in the fractional form (13/5, 12/24, 11/5119,
etc) that then needs to be presentable AND manipulatable. If I type in as
is, the number gets interpreted as a date; if I format as text, the data is
no longer capable of being plugged into functions; if I format as a fraction,
excel interprets and simplifies the fractions and only allows for three
figure denominators. Does anyone know how to get Excel to just trust me that
I know what I'm inputting?

Use three columns -- one for the numerator, one for the slash, one for
the denominator.
That way you can see the input in the form you want. You'll need to
adjust your formulas though to get the right answers when you manipulate
the results.
Excel, like most people, doesn't like improper fractions!

Bill