Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
noyb
 
Posts: n/a
Default Excel converts formula to number

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
CLR
 
Posts: n/a
Default Excel converts formula to number

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



"noyb" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.newusers
CLR
 
Posts: n/a
Default Excel converts formula to number

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



"noyb" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
noyb
 
Posts: n/a
Default Excel converts formula to number

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

noyb wrote:
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

  #5   Report Post  
Posted to microsoft.public.excel.newusers
tim m
 
Posts: n/a
Default Excel converts formula to number

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.

"noyb" wrote:

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

noyb wrote:
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




  #6   Report Post  
Posted to microsoft.public.excel.newusers
David McRitchie
 
Posts: n/a
Default Excel converts formula to number

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"noyb" wrote in message ...
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



  #7   Report Post  
Posted to microsoft.public.excel.newusers
noyb
 
Posts: n/a
Default Excel converts formula to number

Thanks for reply. I've checked ToolsOptions 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?


David McRitchie wrote:
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"noyb" wrote in message ...

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




  #8   Report Post  
Posted to microsoft.public.excel.newusers
David McRitchie
 
Posts: n/a
Default Excel converts formula to number

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]

"noyb" wrote in message
How come when I enter 10000/10 excel displays 1000 in the formula bar



  #9   Report Post  
Posted to microsoft.public.excel.newusers
noyb
 
Posts: n/a
Default Excel converts formula to number

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 McRitchie wrote:
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]


"noyb" wrote in message

How come when I enter 10000/10 excel displays 1000 in the formula bar




  #10   Report Post  
Posted to microsoft.public.excel.newusers
David McRitchie
 
Posts: n/a
Default Excel converts formula to number

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...tm#debugformat
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"noyb" wrote in message ...
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 intact 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Need excel formula to sum 1+2+3...+N given a number N LAdekoya Excel Worksheet Functions 7 May 26th 05 07:48 PM
Excel formula randomly changes to hard-code number Ned Excel Discussion (Misc queries) 3 February 14th 05 11:31 PM
Excel formula divide list of #s by N to get a number above 40 JudyK Excel Discussion (Misc queries) 11 February 9th 05 08:51 PM


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"