Excel converts formula to number

  • Thread starter Thread starter noyb
  • Start date Start date
N

noyb

How come when I enter 10000/10 excel displays 1000 in the formula bar
and not 10000/10? Does not always happen so I guess must be setting.
Thanks
 
If you type 10000/10 and press enter, the cell will display 10000/10.

If you type =10000/10 and press enter, Excel will see that as a formula and
the cell will display the result of the math.

hth
Vaya con Dios,
Chuck, CABGx3
 
If you type 10000/10 and press enter, the cell will display 10000/10.

If you type =10000/10 and press enter, Excel will see that as a formula and
the cell will display the result of the math.

hth
Vaya con Dios,
Chuck, CABGx3
 
Sorry, I didn't mean in the body of the worksheet and I know the
difference between text and numbers. I mean in the (I think its called)
"formula bar" near the top of the screen where the actual formula behind
the number is displayed.
Thanks
 
Are the cells 'protected' and 'hidden'? If you look under help for 'formula
bar settings' then click on 'display or hide formulas' it lists a method for
not displaying formulas in the formula bar by protection and hiding.
 
I think you have managed to turn transition formula entry on in
Tools, Options, Transition(tab), Transition ....
turn off all transition items, they were place there so that
Lotus 1-2-3 spreadsheets or people using them work with Excel
like it was Lotus 1-2-3. Something that should not be used, as
things added to Excel later may be compromised, and you will not
be on same wavelength of others using Excel.
 
Thanks for reply. I've checked Tools>Options and don't have any
transitions checked. I have concluded that the behavior is related to
using a "+" or "=" when entering formulas. Using + at the beginning of
data entry results in the actual formula disappearing whereas using =
does not. I wonder why?
 
Hi ...,
Sorry, certainly should have mentioned that in order to enter
text into a cell, it would be best to format the cells (and such
columns) as Text (format, cells, text). An alternative is
to precede the entry with a single quote.
==
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
 
I don't seem to be asking the question correctly because replys keep
referring to text entry which is not what I am asking, so here goes again.

If you turn off all toolbars you are left with a row of menus (file,
edit, view, etc) and a second row which has a "Name Box", "Insert
Function" icon and an area called "Formula Bar" (at least when I hold
the mouse cursor over it). It is the Formula Bar I am asking about. So,
sometimes when entering data I want to be reminded that the 1000 I see
in the body of the worksheet is actually the result of my entering
10000/10 and usually the formula entered remains in tact in the Formula
Bar but sometimes after entering 10000/10 the Formula Bar and the cell
in the worksheet both show 1000. That is my simple formula is converted
to the result in the Formula Bar. Why?
Thanks

David said:
Hi ...,
Sorry, certainly should have mentioned that in order to enter
text into a cell, it would be best to format the cells (and such
columns) as Text (format, cells, text). An alternative is
to precede the entry with a single quote.
==
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
Click to expand...
 
If you want to see if you have formulas in cells
-- select all cells with Ctrl+A in versions before Excel 2003
if you are using Excel 2003 the gray button will work in this case
-- Ctrl+G (Edit, GoTo), Special, Formulas

You can do the same thing with a macro, and something similar
with Conditional Formatting.

If you are entering 1000/10 into a cell as a formula instead of =1000/10
then you are using transition options to emulate Lotus 1-2-3 and are
not really using Excel properly. This would put you way out of synch with
help from others in the Excel newsgroups.

Now if you look at your last part of your restatement, it appears in conflict with
the first part of your restatement, so I will reiterate some points of
text entry in cells, even though it sounds like that is not what you state
in the first sentences.

There are some minor differences between pasting into the cell and pasting
into the formula bar in that there is no adjustment of addresses if you paste
into the formula bar. You may be typing in a space, or pasting in a Char(160)
character (non breaking space character which is   in HTML).

Some things to look for if you have problems with text vs numbers
1) see if format, cells, shows Text or a Number format
2) see what you get with =ISTEXT(A1) and with =LEN(A1)
3) a hint of whether a cell is text is if left justified, and a number if right
justified, but justification can be overridden or hard to tell, you really
need to check =ISTEXT(A1) or =ISNUMBER(A1)
4) check that you have calculation as automatic -- tools, options, calculation
otherwise things don't change immediately, you may have to close workbook,
or use F9 for recalculations to actually take effect.
5) check that transition (Lotus 1-2-3) options are not on -- tools, options, transition
6) see http://www.mvps.org/dmcritchie/excel/join.htm#debugformat
 
Back
Top