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

Comments page 2. Total comments: 140

Prasath says:
2019-09-19 at 2:05 pm

Hi Svetlana,
I have a challenging requirement. I need to compute ageing of Invoices paid. There are 3 dates involved. Payment date[1] (-) Resolution date[2] is age of the line. If Resolution date is blank, then the the formula should consider Invoice date[3]. I can do it easily in the sheet by using if(res date="blank",Inv date,Res date) and then put a count in cell on the numbers between 0-30, 30-60 etc. But I cannot add a new column in the file as it is a protected file. Can I build a formula for this. Can you please help.
Mohammed says:
2019-07-17 at 1:36 pm

hello,
I need help regarding matching two databases and returning a non-exact value based on a reference value. the first database (sheet 1) is:
Patient Number diagnosis
123321 Hypertension
112233 heart Failure
995566 Diabetes

the second database (sheet 2) is
Patient Number diagnosis
123321 Hypertension stage 1
112233 intrinsic heart Failure
995566 Diabetes mellitus

so as you see, the patient number is the common value of both sheets, but the diagnosis is not exactly the same, is there a way to make excel bring the approximate value (like if 5 or more letters matching) from sheet two and add it as an extra column to sheet 1, as a new cell in the same raw of the same patinet number...

Appreciating your help
Ankur says:
2019-06-26 at 5:37 am

I have a paragraph in Cell A1 & A List of Text strings in Range E1:E100.
I want to put matching text string from E1:E100 to "1st to nth cell in cloumn C

Can you help.
Naman Nishant Rana says:
2019-06-25 at 10:54 am

i have a range of values in the form of "from" and "to" and corresponding to that some quantities, call it array 1. I have another set of values in the same form of "from" and "to" but are the different from the first array, call it array 2. I need values corresponding to array 2 from array 1 by splitting the quantities corresponding to array 1 by dividing the quantities by difference of "from and "to" in array 1.
Tim says:
2019-06-19 at 5:33 pm

Nice article. Keep up.
Brett Pigon says:
2019-06-11 at 9:17 pm

I want to curve fit data with a quadratic polynomial, outside of trendline in plotting. Online sources suggest Linest(Y's,X's^{1,2},1,1) entered as an array formula while 3 cells are selected. I am using Office 2016. This results in the VALUE error all 3 cells. If the exponent instead points at an array variable, e.g. {1,2} entered as an array across two cells, Pointing at the first of the 2 array variable cells gives a valid slope and intercept in two cells, the N/A in the third. Pointing at the 2nd cell of the array formula, it looks like a quadratic fit but still 2 terms, the 3rd with the N/A. Ideas? Thank you.
Don says:
2019-05-15 at 4:55 am

I need to copy down a constant column value (column 1) to an existing sum array?:

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(9, 10, 11 _, 12, 15, 16), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Orkhan says:
2019-03-20 at 5:46 pm

Hi All,
I have a question, not too advanced in excel, but to certain degree. I have a following table:
Column C - I have dates
Column I - I have number of students in training
I need to show in cell K1 the number of students who are in training this week. I came up with a long formula below which does the job, however is long and confusing for others. My question, is there a simpler way to show this by using TODAY and WEEKNUM via array formula or something more simpler than my current string below:

=SUMIFS($I$31:$I$78,$C$31:$C$78,">="&(TODAY()-(WEEKDAY(TODAY(),2))+1),$C$31:$C$78,"<="&(TODAY()+(7-(WEEKDAY(TODAY(),2)))))

Thanks!
Orkhan
Todd says:
2019-03-13 at 9:29 pm

I would like some help, can text from a drop down have a numeric value in a formula. Example 1-Year is the text and the value would be .85. So when I have a formula it would take the .85 x other values? Thank you
Laura says:
2019-02-26 at 8:48 pm

Thank you so much, you are a very good teacher =)
Kireiko says:
2019-02-21 at 8:48 pm

Timesheet Data Help
Hi there! Is there a way to pull data from an excel timesheet that would show who works what shift? My Timesheet is based off of approx 30+ people working various shifts (D or N) on a given day. I would like to be able to see who is working a Day shift on a particular day and conversely a N shift on that same day. I would like to see this data tallied somehow near the end of the timesheet as i already have it set up to count how many people are working a Day shift or N shift but I would like to see the names of the individuals pop up somewhere near the bottom of my timesheet (as a column list not in row format). Is this possible?

An example:

Employee Name:
a D D N N
b D D N N
c D D N
d D D
Day 1 2 2 2 1
Night 1 2 2
(i would like the name of 'a' to show up here - as a table or pop-up somehow?)
same but for both 'a' and 'b'
same but for 'b' and 'c'
I would like the names of 'a' to show up here for nights somehow
same but for 'a' and 'b'
(the start of the above comments indicate the cell location it would have shown up on my excel worksheet but pretend the first three comments are on the same row and the last two are on the row below)
I mean, i *could* do it manually, but that seems really tedious and I'm sure excel has some function to do this for me. I'm just not smart enough to put it together, Lol. I'm thinking vlookup is involved but i'm not sure how to set up my formula right.

Any help is greatly appreciated!

Thank you!

(Ive already posted this to the microsoft help comm forum and theyve already helped me with a VBA script. I am just wondering if there is a formula that can do this same thingÉ)
sarita says:
2019-01-30 at 1:41 pm

Example 1. A single-cell array formula
how it is "20" ? , i got the answer 45
1. Svetlana Cheusheva says:
  2019-01-31 at 8:22 am
  
  Hi Sarita,
  
  Maybe you have different source data? You can try subtracting the numbers in column B from column C with a simple formula like =C2-B2, and see if any difference equals 45.
Shubham singla says:
2018-08-23 at 10:09 am

If i use or function in front istead of using + sybomls between them can they again give same result
Kathe says:
2018-08-08 at 2:50 pm

My Spreadsheet looks like this:

Column Column Column Column Column Column
A B C D E F
2018 2017 2016 2015 2014 2013
1.Link * * * * *
2.JetPro W * * W
3.G&G W W W W W
4.MDBA
5.Bill G. W W W W

Column A; contains the names of the current year winners
Columns B-F; shows there status from Previous years
'*' denotes Honor roll status for that year
'W' denotes they were on the winner list for that year
'a blank cell' they did not make the list that year

In order to receive 'honor roll(*)' it must be their sixth consecutive year on the winners list.

I am trying to write a formula that will highlight 'Column A' to show their status for the current year.
RED highlight for 'new honor roll'
Blue highlight for 'repeat honor roll'
Yellow Highlight for 'new winner'

For Example, referring to the chart above:

#1: Blue Highlight - b/c they have been on honor roll for >6 yrs. consecutively
#2: No Highlight - b/c they lost there status in 2016, so only have 2 consecutive wins (2017&2018)
#3: RED Highlight - b/c 2018 makes their 6th consecutive win; they move to Honor roll status 'new honor roll'
#4: Yellow Highlight - b/c they were not on the previous years winners list
#5:Yellow Highlight - b/c they were not on they previous years winners list
Manish Pandey says:
2018-06-30 at 10:05 am

Hi,

Thank you for this information. I was finally able to solve my problem after going through your tutorial. I did not find here solution to exact problem i was having, but still, applying things taught here, I was able to do it.

Problem: To count number of rows where values in one column (Say A1:A100) is greater than the corresponding values in other column (Say B1:B100).

My Solution is as follows:

{=SUM(IF((A1:A100)>(B1:B100), 1, 0))}

Thanks a lot. Regards.
Kevin says:
2018-06-27 at 7:04 pm

I have an array formula in a VBA routine covering several rows.
I need to copy the VALUE resulting from executing the array formula into another column on the same sheet. However, copy and paste or even paste special values does nothing.

What's so special about getting the values only from an array formula under VBA?
Jugh says:
2018-06-27 at 5:42 pm

Hi, i just tried to edit the =SUM(LARGE(range, {1,2,3})) array formula to
=LARGE(range,{1;2;3}). The idea was to get it to list the 3 largest numbers when i dragged the original cell down 2 cells.

Unfortunately it only displays the biggest number meaning it was only reading the 1 and not the 2 or 3. So it didn't matter how many times i dragged the cell down it would only show me the biggest number.

Can someone explain to me why? , from my understanding i assumed it would naturally follow the constants from 1 to 3 when dragged down, thus giving me the first largest to 3rd largest numbers.
SD says:
2018-06-18 at 7:24 am

Hey Doug - Its actually the second screenshot in this post.
Please check this section "Simple example of Excel array formula".

But anyways, found a way using :

=IF(IFERROR(FIND("30",INDIRECT(CONCAT(CHAR(64+MATCH("Sold",1:1)),ROW(E2))))>0,FALSE),INDEX(A:A,ROW(E2)),"")

So basically , E2 will now have the value at A2,if column SOLD as value 30 in it, dragging the formula down and will repeat it. (need adjustment to 1:1 or use formulas -> name manager to create a named value).

Thanks for your help !
SD says:
2018-06-16 at 4:40 pm

How I can find all products where sold=30 ?
1. Doug says:
  2018-06-16 at 5:01 pm
  
  SD:
  The function to use is the SUMIF function. Where the values to sum are in the range B27:B32 and the values to sum are equal to or greater than 30 the formula is:
  =SUMIF(B27:B32,">=30")
  If the values to sum are only the ones equal to 30 then you just have 30 and no double quotes. =SUMIF(B27:B32,30)
  Use the COUNTIF function in the same way to get the number of products sold. So the formula would look like: =COUNTIF(B27:B32,30)or =COUNTIF(B27:B32,">=30")
  1. SD says:
    2018-06-17 at 4:23 am
    
    Thanks Doug, But that gives me 60, I want to see "Kiwi , Mango"
    1. Doug says:
      2018-06-17 at 4:32 pm
      
      SD:
      Can you post a copy of the columns your data is in?
      It'll help if I can see what you're working with.
Neeraj Sharma says:
2018-05-24 at 12:00 pm

Hi ,

I have the data in below format:

A -100
B -234
C -32

A -123
B -221
D -456

A -145
B -245
C -312
D -478

I want to format this data as:
A B C D
100 234 32
123 221 456
145 245 312 478

Could you please help me how it can be done in excel?
1. Doug says:
  2018-05-24 at 8:54 pm
  
  Neeraj:
  In working briefly with your example data the only thing I can come up with is a procedure that involves Text-to Column then copy and transpose. If you have a lot of data like this, it will be a time consuming major pain to get the job done, but it can be done. Because your data is not in a consistent structure I can't figure out another way to do it without resorting to some code. Even then it would not be easy. Anyway, here's the way it can be done:
  Select each group of the data for example the first three numbers.
  Then under Data choose Text-to-Columns.
  Then, choose the Delimited button then next.
  Then, check the Other box and enter a hyphen in that field, then highlight the first column that contains the letters and choose the Do Not Import button, then Next.
  Now the numbers will be in separate columns. Select these three cells, copy them and then choose Paste Special and select the Transpose button.
  Click Paste and the numbers will be in three separate columns which you can put headers on A,B and C. Go Ahead and put a header on a fourth column as D.
  You'll have to go through this same procedure for each of these with the exception of the second group of data.
  With this group after you've got it in the separate cells, you'll need to just copy the top two numbers and transpose them into the proper cell and then copy the last number into the D column by itself.
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.

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

Comments page 2. Total comments: 140