Comments on: Excel array formulas, functions and constants - examples and guidelines

In this tutorial, you will learn what an Excel array formula is, how to enter it correctly in your worksheets, and how to use array constants and array functions in Excel. Continue reading →

Comments page 2. Total comments: 78

  1. HI,
    MY QUESTION IS ,I CREATE A1 TO A12 MULTIPLE IN RESULT OF B1 TO B12 IN ALL CELL FROM MULTIPLE VALUE OF *3. PLEASE PROVIDE INFORMATION.
    THANKS

  2. I have a list of (+) and (-) in a column, each one needs to be assigned a number based on it's position to the last of it's kind on the list. So the list may have 2 (-) then 3 (+) then another 2 (-). I'd like to run a formula that will assign an odd number to the (-) and an even number to (+) but consecutively. So the first two would be 1,3, the next three would be 2,4,6, then the next two would be 5,7. I've tried setting to columns with the numbers and using an array formula({=if(A1:A8="(-),C1:C8,D1:D8)}, assuming C column has 1,3,5,7,9,11,13 and the D column has 2,4,6,8,10,12,14 listed) the problem I've come across is that it takes it consecutively for the last number so it would go 1,3,6,8,10,11,13 instead of the desired 1,3,2,4,6,5,7. Any ideas how to fix this?

    1. Hi Krista,

      If I understand you correctly you should use the following array formula:

      {=IF(A1="-",SUM(IF($A$1:$A$7="-",1*IF(ROW($A$1:$A$7)<=ROW(A1),1,0),0)) *2 - 1, SUM(IF($A$1:$A$7="+",1*IF(ROW($A$1:$A$7)<=ROW(A1),1,0),0)) *2)}

      Where the values "+" and "-" are in A1:A7.

  3. Hi,

    If the cell A1 is more the 45 to add 15 and if A1 is less than 45 to times/multiply by 1.2

    Please if anyone could kindly help which formula i would have to use

    Many Thanks,

    Abe

    1. Hi Abe,

      If my understanding of the task is right, you can use the following simple formula:

      =IF(A1>45, A1+15, A1*1.2)

  4. Hi,
    I've extracted data specific to employee names from a master sheet using array formula.

    I'm struggling to create a dynamic chart to display this data using excel.

    Any tips on how this can be done?

  5. Is it possible to define an array that is a combination of cell ranges and constants? For example how would one use a a three element array made up of {A2, 2-A2, 1} in a formula where A2 is a cell reference? Seems like it should be easy, but I'm stumped. Thanks!

    1. I have entered this formula and got the correct result:
      {=SUM(A1,2-A2, 1)}

  6. I have an Excel dataset consisting of 500 rows by 7 columns. I am generating additional data points from this dataset. I need to multiply (or other function) each row by all 500 rows, creating 250,000 new rows of data. Each cell needs to function as a constant that is multiplied by all the other cells in the same column (which are not acting as constants). How do I do this efficiently?

    1. I think that an array formula will not help you with this task.

      Please see the following example that may help you:
      https://support.ablebits.com/blog_samples/array-formulas-functions-excel_13_MultiplyEachRow.xlsx

      Enter five values to A1: A5

      Use the following formula to get the first multiplier address in 25 resulting rows:
      ADDRESS(TRUNC((ROW()-6)/5+1,0),1)

      Use this formula to get the second multiplier address:
      ADDRESS(IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)),1)

      To get a value using the address, please enter the INDIRECT function.

      The final formula in rows A6: A31 will be as follows:
      =INDIRECT(ADDRESS(TRUNC((ROW()-6)/5+1,0),1))*INDIRECT(ADDRESS(IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)),1))

      1. That was brilliant, Fedor! I applied your example solution to my dataset and got exactly what I requested. Thanks!

        Unfortunately, I found out that Excel (2007) can only handle 32,000 rows for graphing or statistical analysis. Now am I thinking of condensing the 250,000 rows generated with your formula down to 25,000 rows. This can be done by sorting the dataset, then averaging each successive 10 rows. I tried that, but I am getting a moving average rather than averaging 10 rows to make one new row, then averaging the next 10 rows to make the second new row, and so on.

        Could you suggest an approach? I am sorry to trouble you again, but you are the maestro.

        1. I found a solution that works very well, which I will post in case it is useful to anyone else:
          >>If your data starts from An (where n is 2,3,4,...), use this formula:
          =AVERAGE(INDEX(A:A,n+10*(ROW()-ROW($B$1))):INDEX(A:A,n-1+10*(ROW()-ROW($B$1)+1))
          where you should change n to 2,3,4,...

  7. HI, I am currently studying Design & produce business documents.
    I have an excel sheet with name of supplier, product name, unit price, unit sales and Total sales. I have been told to add an subtotal to this spreadsheet, go to subtotal, click on unit sales and total sales, tick replace current subtotals and Summary below data and apply. It gives me a message I can not change part of an array. How to I get around this? Many thanks Leanne Waldron

  8. I've been using array formulas for 13 years and fear a recent (mandatory) upgrade to 2013 may have broken them. Until now, I could enter a formula like

    =SUM(($A:$A=$A2)*($B:$B=$B2)*($C:$C)*($D:$D))
    {using cntrl-shift-enter, of course}
    This would:
    *Allow me to test on multiple conditions vs. columns A and B (etc.)
    *Combine weighted elements across columns C and D (etc.)

    This is simplified for illustration - test values were often fed from drop-downs, and both conditions and products often spanned numerous columns. All worked well, and the ability to specify an entire column made formula entry easy and flexible in the face of varying data counts. Arrays also offered advantages over pivot tables in always refreshing dynamically and offered more persistent & flexible formatting than the fairly rigid pivot table formatting options.

    Now in Excel 2013, this array formula appears to break as text header rows evaluate to errors. Previously, headers led to false conditions equaling zero, and the sum continued to a correct answer. Now, I encounter errors when using the array format or an incorrect final 0 if entering as a non-array SumProduct function & syntax.

    Are there any options to restore the old evaluation rules (False*Text = 0)? I could be precise in choosing only numeric ranges or rewrite as a set of dynamic named ranges, but that's considerable effort for a number of legacy workbooks.

    Thanks in advance for your help!

    1. Newer Excel versions attempt to 'parse' the TEXT cell content as a numeric value before applying the '*' multiply operator, i.e. as if it was implicitly fed to a VALUE() function call and thus produce a #VALUE error for non-numeric strings.

      The way to stop these errors from propagating into your SUM is to use IFERROR(), i.e.

      {=SUM(IFERROR($A:$A=$A2, 0)*IFERROR($B:$B=$B2, 0)*($C:$C)*($D:$D))}

      should work for you.

      Since you use multiply and add operators as AND/OR 'replacements', it's better to write IFERROR(cond, 0) rather than IFERROR(cond, FALSE) as that 'FALSE' would have to be converted to a numeric zero(0) anyway.

      Note that IFERROR will also filter out (and replace by FALSE=0) any NaN's produced by any calculations in the range (#DIV/0 errors, etc.) but I consider that a benefit here.

  9. Many thanks for the valuable hints.
    I'm trying array formulas with (dynamic)named ranges (column names) from an excel (formated) table and I do not get it to work.
    Is it not possible or is there any trick for getting it?

    1. Im having the same issue. Trying to use array formulas to create ranges for an excel chart. It wont allow me to enter it directly in the chart range/s and when I create a named range using the array formula it doesnt work either. (I dont want to alter the original diplayed dataset or create an additional table using the array formula). Is this possible? Any help would be greatly appreciated!

  10. I need a function to split the letters of a word in different cells vertically in ms excel
    could you help me with this

    1. Hello!

      I think you can use the LEFT and MID functions. For example:

      Extract the 1st letter: =LEFT(A1, 1)

      Extract the 2nd letter: =MID(A1, 2, 1)

      Extract the 3rd letter: =MID(A1, 3, 1)

      And so on.

  11. I have a data with duplicate name in column A and seal no in column which are unique in nature, can you help me in getting the data horizontally with text in column C falling in vertical below the Names.

    Branch Name Rec DateTime Seal_no
    Vinay 18-05-15 15:08 j2437981
    Vinay 18-05-15 15:12 j2437971
    Vinay 18-05-15 15:19 J2416597
    Vinay 18-05-15 15:21 J2454248
    Vinay 18-05-15 15:23 j2435055
    SANDRA 18-05-15 15:08 j2416440
    SANDRA 18-05-15 15:12 j2437984
    SANDRA 18-05-15 15:19 J2437293
    SANDRA 18-05-15 15:21 j2435005
    SANDRA 18-05-15 15:22 J2438075

    Need in horizontal in excel

    Vinay SANDRA
    j2437981 18-05-15 15:08 j2416440 18-05-15 15:08
    j2437971 18-05-15 15:12 j2437984 18-05-15 15:12
    J2416597 18-05-15 15:19 J2437293 18-05-15 15:19
    J2454248 18-05-15 15:21 j2435005 18-05-15 15:21
    j2435055 18-05-15 15:23 J2438075 18-05-15 15:22

    1. Vinay,

      If you want to place the data into separate worksheets based on the name, then please try out our Split Table Wizard.

      If your task is to place all the data into different columns on one worksheet, then you need a VBA script. Sorry we can’t help you with this. Please try to find the solution on mrexcel.com/excelforum.com

  12. Due to the fact that I use the European decimal settings (, for decimal and . for digit grouping) my List separator is ;
    This means that I can only creat vertical arrays, because when I try to create horizontal arrays, for instance ={10,20} and then CTRL+SHIFT+ENTER, I get 10,2 in both cells instead of 10 in the first and 20 in the second.
    How to solve this without changing my decimal setting to a dot?

    Thanks in advance.

    1. Hi Mike,

      This seems to be a common international issue that many users struggle with : (

      Please check out this thread on answers.microsoft.com. The last advice seems to make the best sense:

      If comma is decimal symbol, then \ is used in place of comma in Array.

      If semi colon is decimal symbol, then \ is used in place of semi colon in Array. (Semi colon is row separator)

  13. I´m having problems with the transpose example and using formula debug to get a list of cell values.
    Example:
    formula is =transpose(A3:A15)
    When I hit F9 the cell values are delimited by a \ instead of comma, like this:
    ={“Lebanon”\”Israel”\”Turkey”\”Estonia”\”Sweden”\”Greece”\”Tunisia”\”Russia”\”Middle East”\”Denmark”\”Spain”\”Belgium”\”Poland”}

    Any idea how/where to change that so I actually get a list like this?
    {"Lebanon","Israel","Turkey","Estonia","Sweden","Greece","Tunisia","Russia","Middle East","Denmark","Spain","Belgium","Poland"}

    Using Excel 2013 with English (US) language pack installed on German Win8.1 .

    1. Hi Kristian,

      Most likely the backslash (\) is set as a List separator in your Windows Regional settings. Please check Control Panel > Region and Language > Additional settings. When setting the comma as the List separator, be sure to select some other symbol as the Decimal separator.

  14. Just ran across your blog the other day and it is really insightful. In reference to the "And"/"Or" operator section, is it similar to how you can use a sumproduct and sign to complete a shorter countif without an array?

    ex.
    =SUMPRODUCT(SIGN(FlatD[Grade]=A9)*(FlatD[Attribute]="Projected Spring Lexile")*(FlatD[Value]>=C9))

    Gives me the count of three columns with three different requirements.

    1. Hi Rico,

      Yes, you are right, the SUMPRODUCT function is an alternative to using SUM in array formulas. Your example works with the AND logic, it counts rows when all 3 conditions are met. The SIGN function works similarly to the double unary operator (--) in this case.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)