Array formulas and functions in Excel - examples and guidelines, comments page 3

Comments page 3. Total comments: 140

David says:
2018-04-28 at 2:11 pm

This is very helpful, but I'm struggling with extending it to my application.

I need to see how many hours exist for each person in a month, and then use that person's employee number, look up his (or her) rate, and then multiple total hours by that person's rate -- and then I need to do this for each person who charged that month...to figure out the total cost for that month.

Here is example data:

Emp # Name Rate
11111 John 175
22222 Paul 150
33333 George 125
44444 Ringo 130

May June July
Proj 1
John 10 10 10
Paul 15 20 30
Proj 2
John 11 22 33
George 33 40 40

Proj 3
Ringo 16 22 44
George 44 44 44

xxxx

So, I'd look up John's rate and then multiple it by 22 hours and then George's rate and multiple it by 77, and do this for each row...and then sum up those products.
Frank says:
2018-02-23 at 2:12 pm

Very nicely done tutorial. Easy to follow with good progression of complexity from example to example.

Thank you.
Mark says:
2018-01-30 at 11:11 pm

Hi, I have a spreadsheet for monitoring athletes training. In this I collect the type of training (running, cross-training, gym), how long they trained for (in minutes), and the intensity of exercise (training zones from 1-10). I want to calculate the total "training stress". Normally, this would simply be time multiplied by intensity. However, the training zones are non-liner; zone 4 is not twice as hard as zone 2, zone 10 is not 2.5 times as hard as zone 4. Also, each type of exercise has a different effect. Zone 10 running is harder on the body than zone 10 cycling. I know the exact multiplier for each zone of each sport, I just don't know how to create a formula that will check the exercise type, intensity type, and then multiply by the training stress factor.

The training stress factors are as follows:
Running: 0.17, 0.25, 0.33, 0.50, 0.67, 1.00, 1.33, 1.67, 2.08, 2.50
Cross training: 0.08, 0.13, 0.17, 0.25, 0.33, 0.50, 0.67, 1.00, 1.33, 1.67
Gym: 0.02, 0.04, 0.08, 0.13, 0.17, 0.25, 0.33, 0.50, 0.67, 1.00

So, if I did 60 minutes of zone 4 running (stress factor 0.50), the training stress is 60mins * 0.50 = 30

If I did 60 minutes of zone 4 cross-training [e.g. cycling] (stress factor 0.25) the training stress would be 60mins * 0.25 = 15.

I hope that makes sense.

In my spreadsheet I am inputting the following data: date, training type (run, cross-training, gym), training zone (1-10). I then want to have a final column that calculates the training stress by identifying the training type and training zone, and then multiplying the training time by the training stress factor to give the total training stress.

Does this even make sense, and is it possible?

Thanks
1. Ekaterina Bespalaya (Ablebits Team) says:
  2018-03-02 at 5:45 pm
  
  Hi Mark,
  
  Your task is quite interesting. I’d recommend you first to create a small table that will contain the stress factors for different types of training like the one below:
  
  Suppose this table is placed on Sheet 1, and your table where you are inputting the data is on Sheet 2:
  
  Then please enter the following formula into cell E2 in the table on Sheet 2:
  
  =INDEX(Sheet1!$A$1:$D$11, MATCH(C2,Sheet1!$A$1:$A$11,0), MATCH(B2,Sheet1!$A$1:$D$1,0))*D2
  
  You’ll get the total training stress for a particular activity type:
  
  If you want to get the stress summary by date, then please use the standard Excel Subtotal feature (Data -> Subtotal).
  You can find more information about INDEX and MATCH in this article on our blog. To see how to use the Excel Subtotal feature, please go here.
  
  I hope this information will be helpful for you.
Thabang says:
2017-12-01 at 1:13 pm

Good day. Can some assist. which function is used to auto fill these sheet on the dates side. I need short cut on my excel to auto fill the dates like that I only know control D which is time consuming.
25-Oct-17 Stopped 1203
25-Oct-17 Stopped 1203
25-Oct-17 Stopped 1203
25-Oct-17 Stopped 1203
26-Oct-17 Stopped 1203
26-Oct-17 Stopped 1203
26-Oct-17 Stopped 1203
26-Oct-17 Stopped 1203
27-Oct-17 Stopped 1203
Stopped 1203
Stopped 1203
Stopped 1203
28-Oct-17 Stopped 1203
Stopped 1203
Stopped 1203
Stopped 1203
Stopped 1203
29-Oct-17 Stopped 1203
Stopped 18
Stopped 3001
Stopped 3010
30-Oct-17 Stopped 18
Stopped 3001
Stopped 3010
Stopped 3011
Stopped 3012
1. Gennady Terekhov (Ablebits Team) says:
  2018-01-23 at 2:15 pm
  
  Hello,
  
  We have another article that describes how to fill empty cells in your table. Please have a look at it.
  
  Hope you’ll find it helpful.
udaya says:
2017-11-01 at 6:00 pm

Hi Sir,
I have a data base, It includes columns A to J. From A- Name, Item Ref: Product, Location, Qty, Delivered Qty, Request date, Promised Date, etc.

Under Name there are more customer names and one customer name has more than one records.
Then how can I get displayed all the records related to a customer in a different sheet.
Ex. If I type the customer name Ann in a different sheet, then I need to see all the records available under Ann.
So what is the formula that helps me.

Thanks,
Udaya.
Kuze says:
2017-10-31 at 1:42 am

There is a mistake made in the post. The * is not the logical AND operator when used in array functions. It is simply that the Boolean values when used in arithmetic operators are automatically converted to numeric equivalents. So the section on AND and OR functions is wrong. Take for example the following formula presented in that section:
=SUM((A2:A9="Mike") * ( B2:B9="Apples") * ( C2:C9))

The "A2:A9="Mike"" returns a Boolean which is then converted to a numeric when it is multiplied with "B2:B9="Apples"" which in return is multiplied to "C2:C9". When both "A2:A9="Mike"" and "B2:B9="Apples"" are true, the corresponding value from "C2:C9" is taken into the sum calculation. The expression looks as follows 1*1*C.
1. Svetlana Cheusheva says:
  2017-10-31 at 9:08 am
  
  Hello Kuze,
  
  It is true that when the logical values of TRUE and FALSE are used in arithmetic operations, they are converted to 1 and 0, respectively. But it is also true that in array formulas, multiplication acts as the logical AND function because it follows the same rules as the AND operator. That is, multiplication returns TRUE (1) only when all of the elements are TRUE. If any of the elements is FALSE (0), the result is FALSE (because multiplying by 0 always gives zero).
  
  On the other hand, addition acts as the OR operator because it returns TRUE (>0) if any of the elements is TRUE. And the result is FALSE (0) only when all elements are FALSE.
Larry says:
2017-08-14 at 7:23 pm

I am just not grasping how to get the formula I want, though I know the answer is in there. I want to have a formula look up a specific item (say 'sam's apples) and then fill in the corresponding rows with the last year's sales by month. In other words (currently):
a b c d e
Company Jan Feb March April
1 Sam's apples

2

3

What I want once it pulls the data from a separate spreadsheet:

a b c d e
Company Jan Feb March April
1 Sam's apples $532 $225 $632 $1032

2

3

I have over 2000 rows to sort and the company and the sales $ do not match up. Thanks ahead-
manoj hayaran says:
2017-08-11 at 8:23 am

i want to create this type of table to sort the data
col A B c
type weight colD E F G H
Gr-1 Gr2 1000 Gr1 Gr2 Gr3 Gr4 up to Gr17
Gr2 Gr3 2000 Gr1 1000 4000
Gr3 Gr4 3000 Gr2 2000
Gr1 Gr3 4000 Gr3 3000
up to Gr17 please help
Daniel says:
2017-08-04 at 2:34 pm

Светлана, спасибо за такую развернутую статью, наконец-то исчерпывающее объяснение про формулы массивов!
SST says:
2017-07-31 at 9:09 am

I need to display name of students separated with Comma in one cell holding same grade.
ljubo says:
2017-07-25 at 11:18 pm

Hello everyone!
Dear Svetlana, how to Sum numbers from array of text cells? textNUMBERtext A1:D100

This working with single cell: =LOOKUP(99^99;--("0"&MID(A1;MIN(SEARCH({0\1\2\3\4\5\6\7\8\9};A1&"0123456789"));ROW($1:$1))))
Help please :)
Jiri says:
2017-06-13 at 3:11 pm

Hello, can someone please help me figure out why the following arrayformula works

={OFFSET(INDIRECT("'GEN rates'!E"&MATCH(RIGHT(tool!K23,2)&A2&tool!F21,'GEN rates'!A68:A79&'GEN rates'!B68:B79&'GEN rates'!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!H23)+OFFSET(INDIRECT("'GEN rates'!E"&MATCH(RIGHT(tool!K23,2)&A2&tool!H21,'GEN rates'!A68:A79&'GEN rates'!B68:B79&'GEN rates'!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!F23)+IF(RIGHT(tool!K23,2)="EU",(tool!G23+tool!I23)*'GEN rates'!E80,0)}

while the following one with nested IF returns Value# error?

={IF('GT support'!B11<3,OFFSET(INDIRECT("'GEN rates'!E"&MATCH(RIGHT(tool!K23,2)&A2&tool!F21,'GEN rates'!A68:A79&'GEN rates'!B68:B79&'GEN rates'!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!H23)+OFFSET(INDIRECT("'GEN rates'!E"&MATCH(RIGHT(tool!K23,2)&A2&tool!H21,'GEN rates'!A68:A79&'GEN rates'!B68:B79&'GEN rates'!C68:C79,0)),ROW($A$68)-1,0)*(tool!M23-tool!F23)+IF(RIGHT(tool!K23,2)="EU",(tool!G23+tool!I23)*'GEN rates'!E80,0),"incl.in FOB")}

I have been racking my brains to no avail so far, and seek any kind of advice to make the array formula work under the additional IF condition in the second case (otherwise the formulas are the same)

Many thanks in advance,
supritha says:
2017-05-09 at 3:56 pm

how to give an array as input?
1. Giovanni Ciriani says:
  2017-08-01 at 8:07 pm
  
  just name a range, and use the range name as input.
eon says:
2017-05-02 at 2:51 pm

I'm looking to fill cells in B22 to B29 by changing columns A22 to A29. Can an array do this for the calculation I have set up, or do I need to simplify?

https://docs.google.com/spreadsheets/d/1TiHFXbvwGHvrrBNGVmgN8btwPZ-5OGjtmcY-g-sdXrI/edit?usp=sharing
Dutch says:
2017-01-27 at 10:03 am

In the first example you say the grand total can be calculated with "=SUM(B2:B6*C2:C6)" but this should actually be "=SUMPRODUCT(B2:B6*C2:C6)"
1. Svetlana Cheusheva says:
  2017-02-07 at 9:52 am
  
  Hi Dutch,
  
  This can actually be either, array SUM or SUMPRODUCT, which is an array formula by its nature.
Jeff Lucas says:
2017-01-18 at 4:36 pm

Hello,

I need some help with calculating percentiles while using an array formula. Current formula is:

=IF(B7:B15>6,PERCENTILE(IF(Data!$G:$G=ReportPages!A1,IF(Data!$E:$E=ReportPages!A7:A15,IF(Data!$I:$I>0,Data!$I:$I))),0.1),"*")
ENTER +CTRL + SHIFT

The return value is #N/A

I isolated the issue to the "ReportPages!A7:A15" portion of the formula. It works fine with I change this to reference a single cell. Any help would be great.
Rachid says:
2016-11-28 at 4:16 pm

Hi Svetlana;

Think you for your efforts to make learn Excel tools.I would like to Know if i can put for example {1; 2; 3} in the rows of the Offset function,and what it happened if i had put it and used all formula in sum function.

Best Regart
Mike says:
2016-11-14 at 1:51 pm

Wow!

Am in love with your articles. Very easy to understand.

I dreaded working with arrays initially.

Thanks for putting a smile on my face:-)
Pavan says:
2016-11-11 at 9:51 am

i have data in that i need to lookup 2 values matching data should on third cell and its duplicate data, suppose the data like

DATA
Product mm/dd/yyyy mm/dd/yyyy Sales
apples 11/10/2015 11/11/2016 20
oranges 11/10/2015 11/11/2016 52
banana 11/10/2015 11/11/2016 35
apples 11/10/2013 11/9/2015 66
oranges 11/10/2013 11/9/2015 69
oranges 9/8/2012 10/10/2013 55

Result data would be below

Product Date Required result
Apples 11/11/2014 66
Orange 9/9/2013 55
Danica Rose Oc says:
2016-10-15 at 11:11 am

I am currently doing a project in excel, it is a database for medicine with a running lot no. which means a lagundi table can have many lot no. w/c is based on the date it is produced, now i want to display in the sheet what are the list lagundi with a specific lot no. which first entered the warehouse that has a quantity availble so it is easy to locate? WHAT WILL I DO WHAT FORMULA, HELP ME PLEASE IF I CANT SUBMIT THIS IT IS A FAILING GRADE THAT AWAITS ME. THANK YOU
Uche Uche says:
2016-09-17 at 8:51 am

You are great and as a student of data analysis,I just joined your column by accident and i am enjoying every bit of information you have published in this field.
However, I want to know the real difference between using SUMIFS, COUNTIFS, and AVERAGEIFS to summarize data using multi-criteria and the INDEX+ MATCH COMBINATION.
Thanks.
irfan ali says:
2016-09-10 at 8:55 am

hi dear,
i have one issue kindly help me
i have 2 row
suppose that
10 20 30 40 50 60 70 80 90
15 25 35 45 55 65 75 85 95
greater than formula???
if i put the value in cell (30) and i wanna search the greater value in 2nd row.Which formula i put in cell than show the answer is (35)
because 1st row 30 is greater than 2nd row 35.
kasun charitha says:
2016-07-23 at 11:37 pm

i have a table.
coloumn names( employee name, basic salary, allowance, netsalary)
row names(employee 01, employee 02,.......,employee 10)

i want to extract the employee whose basic salary is grater than $200.

how to do it?
i don't like use a filter method.
Rizan says:
2016-07-06 at 8:43 pm

Need your kind help.
I linked a master sheet (some range of cells) with many other sheets (with the same cell reference range) using arrays. However, some functions are not working in the Master sheet. For eg. sum function does not work out within the cell ranges which are linked. Any ideas????
joh says:
2016-07-04 at 3:21 pm

thanks you all, for puting someone into light
Confused says:
2016-05-13 at 5:08 pm

I have a list of potential % changes in a variable and another list with the probability that % change will occur. If I use the Data Analysis Random Number Generator it does not allow the number to be recalculated and accept changes in the simulation. How do I generate a non-static random % change based on the assigned probabilities?
didier says:
2016-05-11 at 4:21 pm

Hi, need help comparing text (separated by commas) in 2 cells, to see what is different. For example:

A1 = John, Matt, Shelly
A2 = Polly, Shelly

We can see that John and Matt have been removed from A2, however Polly has been added. What is the best way to go about pinpointing the differences between two cells?
atique says:
2016-05-11 at 4:20 pm

Hi, need help comparing text (separated by commas) in 2 cells, to see what is different. For example:

A1 = John, Matt, Shelly
A2 = Polly, Shelly

We can see that John and Matt have been removed from A2, however Polly has been added. What is the best way to go about pinpointing the differences between two cells?
vivek patel says:
2016-04-21 at 6:12 pm

i have a doubt how can we convert a cell that contains a range in to many rows of whole number
demo
3-6 range has to be converted to 3
4
5
6
in similar way i have to convert many ranges please help
amrutha says:
2016-04-08 at 6:46 am

Need Help

Hi am trying to use vlookup to extract muitiple column details for multiple id using array concept but am just able to extract the 1st required column details.

Here is the formula:
{=VLOOKUP(I3:I11,$A$1:$G$278,{2,3,4,5},0)}

help me to identify where am going wrong
1. Renat Tlebaldziyeu (Ablebits Team) says:
  2016-04-08 at 10:39 am
  
  Hi amrutha,
  
  Please show us how your data looks like.
Joao Barros says:
2016-04-07 at 2:37 pm

Hi
i nee to know if a value of array P
1-Jan-15
3-Abr-15
25-Abr-15
1-Mai-15
10-Jun-15
15-Ago-15
8-Dez-15
25-Dez-15

exist in array AK.
01-01-2015 20:00
01-01-2015 23:00
02-01-2015 00:00
02-01-2015 01:00
02-04-2015 23:00
03-04-2015 00:00
03-04-2015 01:00
24-04-2015 23:00
25-04-2015 00:00
25-04-2015 01:00
30-04-2015 23:00
01-05-2015 00:00
01-05-2015 01:00

if exist than indicate with a "D". I tried with the expression
={IF(INT(VALUE(AP10))=(VALUE($AK$10:$AK$17));"D";"xxx")}
but this does not works. can you help? thanks
1. Renat Tlebaldziyeu (Ablebits Team) says:
  2016-04-08 at 10:38 am
  
  Hi Joao Barros,
  
  You should use the following array formula:
  {=IF(SUM(IF(TEXT($B$1:$B$10,"yyyy-mm-dd")=TEXT(A1,"yyyy-mm-dd"), 1, 0)), "D", "xxx")}
  Where the array AK is in B1:B10 and the array P is in A1:A10.
CHANDRAPAL SINGH LODHI says:
2016-04-06 at 4:20 am

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
1. Renat Tlebaldziyeu (Ablebits Team) says:
  2016-04-08 at 10:18 am
  
  Hi CHANDRAPAL SINGH LODHI,
  
  Please show us how your data looks like.
Krista says:
2016-03-17 at 2:29 pm

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. Renat Tlebaldziyeu (Ablebits Team) says:
  2016-04-08 at 10:59 am
  
  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.
parikshit says:
2016-02-24 at 1:29 pm

2 kg + 3 kg = 5 kg.

I want the kg should come once the calculation is completed as above eg. shows
Jason says:
2016-02-24 at 11:52 am

Hi,

Please could you help with a formula.

I am trying to work out a moving average for the following:

Week Commencing Number Value
20/07/2015 7 £2,000.00
27/07/2015 37 £5,000.00
03/08/2015 73 £397,270.97
10/08/2015 40 £238,580.87

So for example week 20/07/2015 I'm using the formula =sum(d2/c2). I want a continuous formula that adds the weekly numbers as each week passes so for the second week the sum would be 7+37 =44 and the value would be £2,000 + 5,000 = 7,000 so £7,000/44.

I require the formula to be continuous as I have many weeks to work out the average.

Thanks for your help
Jason
Abe says:
2016-02-02 at 1:06 am

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. Svetlana Cheusheva says:
  2016-02-02 at 10:37 am
  
  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)
kvex says:
2016-01-31 at 10:28 pm

Can you please explain to me what causes the following inconsistency with a non-array formula?

=LARGE(IFERROR({4,5},0),1) returns 4

BUT when evaluating the IFERROR part using F9 the formula returns:

=LARGE(IFERROR({4,5},0),1)
[F9] =LARGE({4,5},1)
[enter] = 5

Please do not reply that the solution is to enter the formula using Ctrl+Shift+Enter, my problem is more subtle: why does Excel give a different result when the formula is evaluated in 2 steps?
Mubashir Aziz says:
2016-01-20 at 9:49 am

Thanks a lot for this nice article which explains a lot.
Tulsi says:
2016-01-12 at 12:29 am

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?
1. Fedor Shihantsov (Ablebits Team) says:
  2016-01-25 at 11:21 am
  
  I think you should convert your data to Pivot Table and then create a chart.
  Please note, it's necessary to use the Refresh command for a pivot table to update a chart.
  https://support.ablebits.com/blog_samples/array-formulas-functions-excel_15_use_pivot_table.xlsx
Steve says:
2016-01-10 at 12:46 pm

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. Fedor Shihantsov (Ablebits Team) says:
  2016-01-25 at 10:46 am
  
  I have entered this formula and got the correct result:
  {=SUM(A1,2-A2, 1)}
Joe says:
2016-01-05 at 4:03 pm

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. Fedor Shihantsov (Ablebits Team) says:
  2016-01-25 at 10:39 am
  
  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. Joe says:
    2016-02-05 at 10:05 pm
    
    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. Joe says:
      2016-02-26 at 9:31 pm
      
      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,...
Anubhav chaturvedi says:
2015-11-19 at 1:56 pm

Unit COR SCM SMH
CODE 10101 10102 10103
COR 10101 - (411,876,279) 84,314,441
SCM 10102 411,906,311 - -
SMH 10103 (84,314,439) - -

Which formula works to check Difference between COR with SCM, COR with SMH,SCM with COR, SCM with SMH.
Leanne says:
2015-10-21 at 5:15 am

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
1. Maria Azbel (Ablebits Team) says:
  2016-01-25 at 8:33 am
  
  Hello, Leanne,
  
  Please have a look at this article:
  https://support.microsoft.com/en-us/kb/107905
Chad says:
2015-08-21 at 8:29 pm

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. Ger Hobbelt says:
  2016-08-23 at 9:09 pm
  
  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.
Juan Rivas says:
2015-08-18 at 9:38 am

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. Alexander Frolov (Ablebits Team) says:
  2016-08-30 at 10:17 am
  
  Hello Juan,
  
  Please send us a workbook with the sample data and formulas to support@ablebits.com. We'll try to help.
2. QB says:
  2016-08-29 at 3:27 pm
  
  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!
Shankardev says:
2015-07-31 at 10:14 am

Thanks for all the effort put into developing the tutorial. It write-up and examples and explanations are extremely clear and easy to understand.
abcd says:
2015-07-30 at 7:48 pm

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. Svetlana Cheusheva says:
  2015-07-31 at 11:22 am
  
  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.
vinay says:
2015-05-18 at 12:38 pm

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. Alexander says:
  2015-05-18 at 5:26 pm
  
  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
Mike says:
2015-03-12 at 2:25 pm

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. Svetlana Cheusheva says:
  2015-03-13 at 10:26 am
  
  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)
Kristian says:
2015-03-05 at 2:54 pm

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. Svetlana Cheusheva says:
  2015-03-09 at 11:42 am
  
  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.

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

Comments page 3. Total comments: 140