Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Entering Array in Excel

  Asked By: James    Date: Dec 30    Category: MS Office    Views: 830
  

I'm new in using Excel, so I apologize if my question is too easy for
you. I tried to enter this formula in Excel (cell A1):

=sum({1,2,3})

but Excel provided error message and gave the proposal correction

=sum({1,23})

I don't know what the problem is.

Please help me, because if I can't do it then I can't learn using
array in Microsoft Excel further.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Roxanne Dixon     Answered On: Dec 30


Try using backslashes in place of commas in your formula.

It has to do with your international settings. In some locales, the comma is
used as a separator for thousands, and used as a decimal in others. I suspect
your locale uses the comma as a decimal.

Anyway, I tried the following and they all worked for me:

=sum({1,2,3}) 'exactly as your example
=sum({1;2;3}) 'semicolons instead of commas
=sum(1,2,3) 'no curly braces
=sum({(1),(2),(3)})
=sum((1),(2),(3)) 'extra parenthesis around each element

The following did NOT work
=sum(1;2;3) 'semicolons without the curly braces

In looking at the Excel helpfile, apparently the comma is used to separate
values in columns, and the semicolon is used to separate rows (multidimensional
array). The curly braces are used to define array  constants.

I was curious, so I tried setting my locale to France (under the Windows
control panel "Regional") and I was able to recreate your problem. It was very
interesting that when I changed the regional setting, the commas that had been
in my formula  were changed to backslashes (\). The formula worked fine with
backslashes. I changed my regional setting back, and the backslashes did not
work. I did notice, however, that when I reload my spreadsheet, the proper
separator is used based on my regional settings. In other words, if you save
your workbook with the backslashes, and send your workbook to someone in the US,
commas will appear instead of the backslashes in your formula.

 
Answer #2    Answered By: Elisa Larson     Answered On: Dec 30

You don't say which version of MS-Excel you are using. I am using
MS-Office 2003 and it works perfectly in this version.

Though your example is not a good example of an array  formula, good
luck with your learning.

 
Didn't find what you were looking for? Find more on Entering Array in Excel Or get search suggestion and latest updates.




Tagged: