Excel won't let me type a formula using commas!

Discussion in 'Microsoft Excel Discussion' started by brandon_05mn, Jul 15, 2009.

  1. brandon_05mn

    brandon_05mn Guest

    Please help, this is my problem. Some formulas don't work when I type them in.

    For example, when I type this formula, it will say there is an error.
    "=SUM(1,2,3)"
    That does not work, it says invalid format.
    but if I do a formula with a range (instead of commas), it works. Such as
    "=SUM(A5:C5)" - that works just fine.

    I am guessing this has something to do with the commas in the formula. When
    I start to type a formula, a formula helper thing will be displayed that is
    showing the format of the formula. This format example does not include
    commas. It uses spaces instead. So I tried to type the formula with spaces
    (instead of commas) and that doesn't work either.

    I consider myself a advanced user of excel, I have extensive experience with
    excel formulas, so this is really bothering me (especially since I can't type
    many formulas because they need commas and that doesn't work).

    One more thing. I have existing spreadsheets that I am using (I have created
    these months ago). On these, I have formulas that contain commas and they all
    work. But if I double-click on the cell (as if I am going to edit the
    formula, but I don't edit it, I keep it the same) and press enter, the same
    error appears. If I hit escape, it cancels the formula edit request and goes
    back to how it was.

    I did try to uninstall and reinstall excel, that didn't work. But I think it
    is still pulling my info from somewhere.

    Thanks for all of your help.
     
    brandon_05mn, Jul 15, 2009
    #1
    1. Advertisements

  2. brandon_05mn

    Dave Mills Guest

    Could this be related to regional settings? The comma is the decimal delimiter
    in most of Europe.

    On Tue, 14 Jul 2009 18:55:01 -0700, brandon_05mn
    <> wrote:

    >Please help, this is my problem. Some formulas don't work when I type them in.
    >
    >For example, when I type this formula, it will say there is an error.
    >"=SUM(1,2,3)"
    >That does not work, it says invalid format.
    >but if I do a formula with a range (instead of commas), it works. Such as
    >"=SUM(A5:C5)" - that works just fine.
    >
    >I am guessing this has something to do with the commas in the formula. When
    >I start to type a formula, a formula helper thing will be displayed that is
    >showing the format of the formula. This format example does not include
    >commas. It uses spaces instead. So I tried to type the formula with spaces
    >(instead of commas) and that doesn't work either.
    >
    >I consider myself a advanced user of excel, I have extensive experience with
    >excel formulas, so this is really bothering me (especially since I can't type
    >many formulas because they need commas and that doesn't work).
    >
    >One more thing. I have existing spreadsheets that I am using (I have created
    >these months ago). On these, I have formulas that contain commas and they all
    >work. But if I double-click on the cell (as if I am going to edit the
    >formula, but I don't edit it, I keep it the same) and press enter, the same
    >error appears. If I hit escape, it cancels the formula edit request and goes
    >back to how it was.
    >
    >I did try to uninstall and reinstall excel, that didn't work. But I think it
    >is still pulling my info from somewhere.
    >
    >Thanks for all of your help.

    --
    Dave Mills
    There are 10 types of people, those that understand binary and those that don't.
     
    Dave Mills, Jul 15, 2009
    #2
    1. Advertisements

  3. brandon_05mn

    Pete_UK Guest

    Try it using a semicolon instead of comma:

    =SUM(1;2;3)

    Hope this helps.

    Pete

    On Jul 15, 2:55 am, brandon_05mn
    <> wrote:
    > Please help, this is my problem. Some formulas don't work when I type them in.
    >
    > For example, when I type this formula, it will say there is an error.
    > "=SUM(1,2,3)"
    > That does not work, it says invalid format.
    > but if I do a formula with a range (instead of commas), it works. Such as
    > "=SUM(A5:C5)" - that works just fine.
    >
    > I am guessing this has something to do with the commas in the formula. When
    > I start to type a formula, a formula helper thing will be displayed that is
    > showing the format of the formula. This format example does not include
    > commas. It uses spaces instead. So I tried to type the formula with spaces
    > (instead of commas) and that doesn't work either.
    >
    > I consider myself a advanced user of excel, I have extensive experience with
    > excel formulas, so this is really bothering me (especially since I can't type
    > many formulas because they need commas and that doesn't work).
    >
    > One more thing. I have existing spreadsheets that I am using (I have created
    > these months ago). On these, I have formulas that contain commas and theyall
    > work. But if I double-click on the cell (as if I am going to edit the
    > formula, but I don't edit it, I keep it the same) and press enter, the same
    > error appears. If I hit escape, it cancels the formula edit request and goes
    > back to how it was.
    >
    > I did try to uninstall and reinstall excel, that didn't work. But I thinkit
    > is still pulling my info from somewhere.
    >
    > Thanks for all of your help.
     
    Pete_UK, Jul 15, 2009
    #3
  4. Hi,

    There is nothing wrong with the formula you showed us unless commas don't
    mean separaters.

    Try choosing Tools, Options, International, and checking to see what decimal
    separator and thousands separator you are using. You can uncheck Use system
    separators and then change the above options. These are related to your
    system settings.

    --
    If this helps, please click the Yes button.

    Cheers,
    Shane Devenshire


    "brandon_05mn" wrote:

    > Please help, this is my problem. Some formulas don't work when I type them in.
    >
    > For example, when I type this formula, it will say there is an error.
    > "=SUM(1,2,3)"
    > That does not work, it says invalid format.
    > but if I do a formula with a range (instead of commas), it works. Such as
    > "=SUM(A5:C5)" - that works just fine.
    >
    > I am guessing this has something to do with the commas in the formula. When
    > I start to type a formula, a formula helper thing will be displayed that is
    > showing the format of the formula. This format example does not include
    > commas. It uses spaces instead. So I tried to type the formula with spaces
    > (instead of commas) and that doesn't work either.
    >
    > I consider myself a advanced user of excel, I have extensive experience with
    > excel formulas, so this is really bothering me (especially since I can't type
    > many formulas because they need commas and that doesn't work).
    >
    > One more thing. I have existing spreadsheets that I am using (I have created
    > these months ago). On these, I have formulas that contain commas and they all
    > work. But if I double-click on the cell (as if I am going to edit the
    > formula, but I don't edit it, I keep it the same) and press enter, the same
    > error appears. If I hit escape, it cancels the formula edit request and goes
    > back to how it was.
    >
    > I did try to uninstall and reinstall excel, that didn't work. But I think it
    > is still pulling my info from somewhere.
    >
    > Thanks for all of your help.
     
    Shane Devenshire, Jul 17, 2009
    #4
  5. brandon_05mn

    Simon Murphy Guest

    Brandon
    Check your regional settings in Windows. It sounds like your culture
    setting have been changed.

    If its not that then check that Lotus compatibility has not been set for
    the affected sheets.

    Cheers
    Simon
    Excel development website: www.codematic.net


    brandon_05mn wrote:
    > Please help, this is my problem. Some formulas don't work when I type them in.
    >
    > For example, when I type this formula, it will say there is an error.
    > "=SUM(1,2,3)"
    > That does not work, it says invalid format.
    > but if I do a formula with a range (instead of commas), it works. Such as
    > "=SUM(A5:C5)" - that works just fine.
    >
    > I am guessing this has something to do with the commas in the formula. When
    > I start to type a formula, a formula helper thing will be displayed that is
    > showing the format of the formula. This format example does not include
    > commas. It uses spaces instead. So I tried to type the formula with spaces
    > (instead of commas) and that doesn't work either.
    >
    > I consider myself a advanced user of excel, I have extensive experience with
    > excel formulas, so this is really bothering me (especially since I can't type
    > many formulas because they need commas and that doesn't work).
    >
    > One more thing. I have existing spreadsheets that I am using (I have created
    > these months ago). On these, I have formulas that contain commas and they all
    > work. But if I double-click on the cell (as if I am going to edit the
    > formula, but I don't edit it, I keep it the same) and press enter, the same
    > error appears. If I hit escape, it cancels the formula edit request and goes
    > back to how it was.
    >
    > I did try to uninstall and reinstall excel, that didn't work. But I think it
    > is still pulling my info from somewhere.
    >
    > Thanks for all of your help.
     
    Simon Murphy, Jul 18, 2009
    #5
  6. brandon_05mn

    Harlan Grove Guest

    Simon Murphy <> wrote...
    ....
    >If its not that then check that Lotus compatibility has not been set for
    >the affected sheets.

    ....

    ?!

    Neither Lotus 123 nor Lotus Symphony (the original, not the new OOo
    1.x clone) were ever afflicted with space as argument separator. Have
    you really seen this behavior due to Lotus compatibility being
    activated and fixed by Lotus compatibility being deactivated?
     
    Harlan Grove, Jul 19, 2009
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dave Peterson

    Re: Opening then saving a CSV file looses Commas in Excel 2003

    Dave Peterson, Apr 22, 2005, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    652
    Dave Peterson
    Apr 22, 2005
  2. Earl Kiosterud

    Re: Opening then saving a CSV file looses Commas in Excel 2003

    Earl Kiosterud, Apr 23, 2005, in forum: Microsoft Excel Discussion
    Replies:
    0
    Views:
    254
    Earl Kiosterud
    Apr 23, 2005
  3. Brian

    Selecting Text Between Commas in Excel

    Brian, Nov 28, 2006, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    1,989
    JE McGimpsey
    Nov 28, 2006
  4. Guest

    How to convert numbers using dots into numbers using commas

    Guest, Dec 5, 2006, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    555
    Guest
    Dec 9, 2006
  5. walterbyrd

    Can not replace commas - "formula too long"

    walterbyrd, May 7, 2009, in forum: Microsoft Excel Discussion
    Replies:
    7
    Views:
    262
    walterbyrd
    May 8, 2009
Loading...

Share This Page