How do I stop Excel from 'interpreting' my data?

  • Thread starter Thread starter Saven
  • Start date Start date
S

Saven

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
 
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.
 
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
 
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
 
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 said:
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?
Click to expand...

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
 
Back
Top