Array Formulas Book Giveaway

It’s August already! How did that happen? Did you know that August is not only a month name, it’s an adjective that means “inspiring reverence and admiration; venerable, impressive”.

One way to build admirable Excel skills is to attend Chandoo’s online PowerPivot course, and enrolment opens today. In addition to the basic course, there is a new advanced level course, taught by Rob Collie, who used to work on Microsoft’s PowerPivot team.

In Excel, it’s also impressive to know how to use array formulas. If you’d like to develop august array skills, here’s your chance to win an e-book copy of Mike “ExcelIsFun” Girvin’s new book, Ctrl+Shift+Enter: Mastering Excel Array Formulas.

Array Formula Basics

Throughout the book, Mike builds a set of Array Formula Efficiency Rules. The first rule is that you should have a basic knowledge of how Excel formulas work, and understand the default number formatting and alignment rules. That gives you a solid foundation for adding advanced formulas to your Excel skills.

Mike starts with array formula basics, and shows how to create a simple calculation that finds the MAX for a set of cells, where one is subtracted from the other in each row. Instead of adding a column to show the differences, and then determining the MAX for the new column, Mike shows how to do it all in a single cell, using Ctrl+Shift+Enter, to create an array formula.

{=MAX(D3:D6-C3:C6)}

When Should You Use Arrays?

In his book, I like the way that Mike carefully explains the advantages and disadvantages of array formulas, compared to non-array. You’ll get a list of questions to ask yourself, and the answers will help you decide which solution might be best. Sometimes there isn’t a clear-cut answer, but at least you can make an educated decision.

For example:

• Is there a built-in function that can do the same thing more efficiently?
• Could you use a helper cell formula?
• Have you timed the various formula options? For spreadsheets that contain many formulas
with large numbers of cell references and calculations, you might need to time different
formula options to see if one is significantly faster.

Everything You Ever Wanted to Know

If you are an Excel formula guru, and work with array formulas every day, you might not need this book. However, Mike has filled this book’s 352 pages with a dazzling collection of examples, so even the function masters might learn a new trick or three.

If you’re comfortable using Excel, and want to explore a fascinating new area, you’ll gain lots of new knowledge from Mike’s Ctrl+Shift+Enter book. Everything is explained in detail, with color-coded screen shots, and you can download the sample files to follow along with the instructions.

If you work on a chapter each day, you’ll be an array formula master in just a few weeks. By the end of the summer, you’ll be ready to tackle the formula challenges in the last chapter. For example:

• lookup the first item in a row with empty cells
• rank values with one condition
• look up the longest word in a column

Enter the Giveaway

Bill Jelen (Mr. Excel) is the book’s publisher, and he has generously donated 4 e-book copies for this giveaway. If you’d like a chance to win a copy, please read the rules, and then make a comment below.

• In your comment, tell us one of your favourite Excel functions, and if you have ever used that function in an array formula.
• The deadline is Wednesday, August 7th, 2013, at 12 noon Eastern Daylight Time.
• One entry per person.
• The 4 winners will be announced on Thursday, August 8th, 2013.
• Each winner will have 24 hours to claim the prize, and if not claimed, another name will be selected.

___________________

84 Responses

1. Vaibhav Garg says:

My the most favorite formula in excel is SUMPRODUCT. Being an excel enthusiast, SUMPRODUCT is the most versatile function imaginable, with all kinds of creative usages.

And yes, I have used it in several array formulae, for example in a custom filtered list and others.

2. Vitalie says:

Gotta be VLOOKUP for me. I think i tried using it in an array once but it didn’t work (maybe I was expecting too much from it). I discovered array formulas fairly recently and still experiment with them (not always successfully I’m afraid :) ). Hopefully the book (if I win one) will bridge the gap in my knowledge.

3. Vasim says:

Fav function is definitely =GETPIVOTDATA

Fav array formula definatly.

={IFERROR(INDEX(\$B\$1:\$B\$100,SMALL(IF(\$A\$1:\$A\$100=\$D\$5,ROW(\$A\$1:\$A\$100)),ROW(A1))),””}

4. Henk Doorlag says:

That’s no fair – my favorite Excel formula is index, followed by match (also known as index/match)
I’m most proud of a 2D convex hull tool I made. It calculates the angle between a line segment and all the line segments to all other points in the data set, selects the point with the largest angle – and uses the new line segment as input for the next one.
The main formula is:
{=MIN(IF((((\$B\$2:INDEX(\$B:\$B;\$M\$1))=F3)*((\$C\$2:INDEX(\$C:\$C;\$M\$1))=G3));””;((((F2-F3)*((\$B\$2:INDEX(\$B:\$B;\$M\$1))-F3))+((G2-G3)*((\$C\$2:INDEX(\$C:\$C;\$M\$1))-G3)))/(SQRT(((\$B\$2:INDEX(\$B:\$B;\$M\$1))-F3)^2+((\$C\$2:INDEX(\$C:\$C;\$M\$1))-G3)^2)))))}

With the input data in columns B and C (A contains headers)
This formula first ignores data points coincident with the last data point [IF((((\$B\$2:INDEX(\$B:\$B;\$M\$1))=F3)*((\$C\$2:INDEX(\$C:\$C;\$M\$1))=G3));””]

Then sets up (part of) the inproduct for the line segment between the last two points and all other points in the data set
[((((F2-F3)*((\$B\$2:INDEX(\$B:\$B;\$M\$1))-F3))+((G2-G3)*((\$C\$2:INDEX(\$C:\$C;\$M\$1))-G3)))/(SQRT(((\$B\$2:INDEX(\$B:\$B;\$M\$1))-F3)^2+((\$C\$2:INDEX(\$C:\$C;\$M\$1))-G3)^2))))]

So the minimum is found, which corrosponds to the maximum angle.

A second column then matches this minimum in the whole list to identify the actual data point that provides the largest angle.
I could have done that in a single cell, but users complained they couldn’t follow the process…

I’ve also expanded the tool to be able to provide a continuous convex hull, removing previously found points to spiral to the center – and to add a buffer to the minimum hull, e.g. all points within 15% of the absolute outer hull.

So index is used to set up a dynamic range (to keep the tool fast (it runs in seconds on at least 15k data points) and to cover the undefined number of data rows) by [\$B\$2:INDEX(\$B:\$B;\$M\$1)] (where \$M\$1 contains a count of the records in column B)
Then index is used to retrieve the datapoint based on these dynamic ranges.
By returning a range rather than a result, index is for me the favorite!

5. Eamon says:

My favourite functions change from time to time. I always enjoy the power of VLOOKUP() and only recently discovered the ability to get it to read Right-to-left. =VLOOKUP(F2,CHOOSE({1,2},C2:C4,A2:A4),2,0). The CHOOSE() function allows me to define an array and I manually create an array first selecting the column with my matching key (C2:C4) and then selecting my column with the output value (A2:A4). It isn’t exactly an array formula but it is close.

6. Patrick says:

One of my favorite functions is INDEX; I can still be surprised by what you can do with it. I started using it by replacing all VLOOKUPs with INDEX+MATCH, but discovered a lot more possibilities.

Alas, I’ve never succesfully used it in an array formula before; I understand the basics of array formulae and use them if I can, but they’re not (yet) intuitive to me. The book might help with that ;-)

7. Niyaz says:

INDEX AND VLOOKUP are my most used to. I am new to array formulas and haven’t used them yet (other than just for getting to know them). Have recently started showing more interest towards PowerPivot and DAX.

8. Tony Huby says:

The one I use most often is the staight forward LOOKUP. Not used it in an array (not had the need yet) but would be interested to know more about array formulas

9. Vinayak says:

I often find myself using INDEX and MATCH functions. I have not yet managed to put INDEX and/or MATCH functions work in array forumula. I’d love to learn to use Array forumula.

10. Vinayak says:

I use INDEX and MATCH functions on regular basis. I have not yet managed to put them to work with array formulas. I’d love to learn to use arary formulas and their combination with other functions.

11. JoAnn Paules says:

Tough question! I’m torn between VLOOKUP and IF. The IF function is simple and can be very handy. VLOOKUP is a true workhorse.

I’ve used an array formula once or twice. I needed to do something that I didn’t know how to do and a quick search on the web pointed me to a solution that used array formulas. I’d love to be able to understand them and put them to use properly. It’s great to have a solution to one specific problem but if I don’t really know what I’m doing, that doesn’t help me down the road.

12. David Tyler says:

tell [me] one your favourite Excel functions
How did you know my go-to pick-up line at a party?

It’s hard to pick one favorite, but I’m a fan of OFFSET. I like the ability to refer to a single cell, or to a dynamically create a range.

I can’t remember offhand if I’ve used OFFSET in an Array Formula before. When writing CSE formulas, I know I’ve created ranges using something like `ROW(INDIRECT("1"&COUNT(A1:E1)))`, so I don’t know that I’d need OFFSET. I guess I’d better read Mike’s book to find out if I need it!

13. Pete Hopkinson says:

I like the “indirect” function when buidling easy to use reports with dynamic ranges.

Not used it in an array yet though….

14. Iyke Kanu says:

Vlookup will be my favourite function, I use sumproduct in array form

15. Raffaele says:

The FREQUECY function used to do unique count, also the INDEX used in a reference contex to create dynamic ranges and probably some other 2 dozens of functions that I do no mention becouse I do not want to bore people :)

16. Mark says:

I would have to say substitute() is my favorite at the moment… it has helped me adjust code that was formatted for indentation using spaces rather than indents, common misspellings, etc. I’ve never used it in an array formula, and to be honest, I’m not very sophisticated with arrays in the first place. This book would be perfect for me! Thanks very much for the contest.

17. Cecilis says:

SUMPRODUCT is my preferred one to work with very complex multiple condition formulas. To keep it simple in an array example, =SUMPRODUCT(–(A1:A50={“Black”,”White”} would retrieve the COUNT of the items in A1:A50 that are either Black OR White.

18. My favorite is BESSELJ. Just kidding, I have no idea what that does. My real favorite is SUMPRODUCT. And, of course, I’ve never used it in an array function because it already operates as an array function.

19. Dave Roberts says:

Index/Match. It’s a family affair. I don’t know how to use array formulas unless the web page I’m on says to use it as an array formula. I’m hoping the book can enlighten me.

20. Travis says:

My favorite is =sumproduct. Use it all the time for creating interactive dashboards that users can select different criteria to show information relevant to them.

If you think know everything about Excel you haven’t explored it enough. Thanks for the great content you provide us.

21. Kevin Vaughn says:

I should not have read the other comments before submitting mine because all of the favorites I would have used are already taken!

I guess I would choose the IF function. More significantly, the nested if. I love how you can build a formula to do what you want, seemingly, no matter how complicated.

As for using it in array formulas, I don’t recall. Probably not because I usually use array formulas that others have created and shared. I don’t remember creating an array formula from scratch. Which is why I need to win that book (ebook!)

22. Oscar Noriega says:

I use the array when using the MATCH and INDEX functions when looking up values in a list. Works great.

23. Sam says:

On a range of unsorted data containing duplicates the below formula array entered performs a High Low binary search to return an array pair containing the position of the first and the last instance of the searched item

=MATCH({1,2},1/(RngToSearch=WhatToSearch),{-1,1})

If the data is sorted then the formula can be simplified further

=MATCH(1,1/(RngToSearch=WhatToSearch),{-1,1})

24. Doug Steele says:

Don’t make fun of me, Debra, but I occasionally use Excel to parse long text strings into multiple other columns by using various text functions such as Search, Left, Right and Mid. I then copy and paste the value (as opposed to the formula) so that I can then import the parsed data into Access, where I can work with it. Sometimes it just easier. Can’t say I’ve tried doing it using array formulae though.

25. Trouttrap2 says:

For me, it would have to be the ‘=IF’ function. ‘=IF’ is so universal for testing conditions in arrays.
{=IF(D2=SomeArray,IF(E2= SomeArray,Result))}

26. Yakov says:

Wow, lots of complicatef formulas on offer here. It’s hard to think of a favourite, actually. Most of the time when faced with a problem I’m thinking, “Why don’t I ever get any easy questions?” That being the case, my most often used ‘first port of call’ is a SUMIF. I use it in too many ways so an example wouldn’t be representative. And no, I haven’t used it in an array formula. But I would love to learn how. Ahem, cough, hint hint.

Cheers from London.

27. JeanMarc says:

Though question… difficult to name only one function. I really like INDEX and MATCH but if I have to name one, I’ll go with SUMPRODUCT because it is very powerful.

28. John Fairlie says:

My favorite is SUMIFS. It used to be SUMPRODUCT but now I find myself using SUMIFS much more. And no I don’t use it as part of an array function.

29. Jorge Rodríguez says:

INDEX AND VLOOKUP are my most used functions. I am new to array formulas and haven’t used them yet, so win this ebook will be a great way to know them.

30. Venus says:

Well thank you Bill Jelen for supplying the books and thank you Debra for sponsoring the giveaway!
I have to say SUMPRODUCT, but that could be that I only recently learned how powerful it is. I have a lot of IF functions floating in my workbooks too. I can’t say I’ve used either in an array as yet.

31. David Patton says:

Average such as Averag flow by season and year

{=AVERAGE(IF(ISNUMBER(MATCH(\$B\$2:\$B\$15283,Spring_,0)),IF(\$D\$2:\$D\$15283=G4,\$E\$2:\$E\$15283)))}

My favorite function is =if(and and also = if(or but I also love Vlookup and Proper and Today!!! I have never used any of these in an array because I am pretty clueless about using arrays in a function but would love to learn by reading the book!!

33. General Ledger says:

Asking me to choose a favorite function is like asking me to choose a favorite child. The criteria might be the same. The one that is successful and doesn’t keep me awake at night.

So many here have praised some great functions. I’ll go with the basics: =, +, -, * and /. Without these, nothing else would be possible.

34. Rick Howard says:

My favorite is simple. {transpose} Use it all the time.

35. Ronald Black says:

I would have to say the use of INDEX and MATCH in arrays are my favorites along with LOOKUP arrays. I have seen that several times on Excel tests and the use of Ctrl Shift Enter in scenarios helped me get a better feel of how powerful array formulas are. I can’t wait to look more into them with Mike’s new book.

36. I think a combination of these functions into an array entered formula resulting in duplicate entries is one of my best used formulas, I use this for making dashboards at times.

=IFERROR(INDEX(\$A\$1:\$D\$34,SMALL(IF(LOOKUPNAME=\$A\$5:\$A\$34,ROW(\$A\$5:\$A\$34),””),ROW(A2)),COLUMN()-4),””)

Would love to get a hold on Mike’s book , as it would be great to master array formulas…

37. mfexcel says:

my favorite was Subtotal which is now replaced by Aggregate… :)
but never used them in an Array.

38. Pavel Lasák says:

Complicated question – my favorite is SOUČIN.SKALÁRNÍ – name in Excel CZ (EN name SUMPRODUCT). Another interesting function VLOOKUP, INDEX, SUMPRODUCT, MATCH, OFFSET.

39. Rudi says:

Using array formulas does seem to elevate one to the Excel guru status around the office… Thats the cool thing about them.

Since the entry of SUMIFS and COUNTIFS in Excel, these array formulas have taken a backseat in the fray, but one thing that still keeps them in the hunt is that they can be manipulated to sum (or count) with an OR condition, where SUMIFS and COUNTIFS cannot handle this (but only AND conditions).

An example of an array with an OR condition is as follows:

{=SUM(((B4:B8=”East”)+(B4:B8=”North”)+(B4:B8=”South”))*G4:G8)}
This will sum sales for three areas. The ‘+’ signs create the OR conditioning.

I like to change the syntax to:
{=SUM((B4:B8={“East”,”North”,”South”})*G4:G8)}
This makes it easier to read and edit.
Note that the inside braces {} are typed out!
After the formula is types, you still use CTRL+SHIFT+ENTER to confirm.

40. Jan says:

I’ve seen a lot before but mine favorite is also the 2D vlookup with using the functions INDEX and MATCH

The second one is the translated one in Dutch :). In the dutch version you need to replace the , (comma) into ; (semicolon). Yeah indeed very annoying!

You can also configurate the settings in windows by using the control panel –> Region and Language –> Formats –> Additonal settings –>
and change the List separator from a semicolon to a comma.

{=IFERROR(INDEX(RawData!\$J:\$J,MATCH(SummaryData!\$A6&SummaryData!G\$4,RawData!\$A:\$A&RawData!\$E:\$E,0)),0)}

{=ALS.FOUT(INDEX(RawData!\$J:\$J;VERGELIJKEN(SummaryData!\$A6&SummaryData!G\$4;RawData!\$A:\$A&RawData!\$E:\$E;0));0)}

41. Erica says:

I often use the INDEX MATCH function; it’s more flexible than a VLOOKUP. I like to use the INDEX MATCH along with EXACT in an array formula; this is perfect for referencing data exported from a Salesforce.com report on the ID field since the ID fields are case sensitive.

{=INDEX(RETURN VALUE,MATCH(TRUE,EXACT(MATCH FROM,MATCH),0))}

42. Nick says:

My favorite functions have to be the VLOOKUP, INDEX, and MATCH. I use INDEX and MATCH in several array formulas such as –>

={((INDEX(\$F\$3:\$F\$500,MATCH(1,(\$B\$1=\$A\$3:\$A\$500)*(\$C4=\$E\$3:\$E\$500),0))),0)}

43. Ryan says:

Bill aka Mr. Excel – you Rock! Thank you for the chance; Context/blog, you also Rock!! Thank you for the opportunity.
Favorite function right now is IF(location = specification, data if true, data if false (which can be nested IFs up to 64… may not be the coolest but for my caliber offers a lot of searching and filtering for repetitive data I want result to do… makes my boss’s text (EA, FT, C, M) turn into a math formula I set.
Just starting out in array’s – could really use the tutelage :)
I love to learn, and really appreciate Chandoo and many other excel guroos out there – keep rockin, we love the time and efforts ya’ll bring to the net table.

44. Brok says:

My favorite functions in Excel are the Index/Match Combo used to replace VLookups. Can’t believe how awesome they are. Can’t say I know how to use them in an array, that’s why it would be amazing to get a copy of the book. I’m still trying to wrap my head around Chandoo’s most recent challenge. Either way, Excel is fascinating!

45. Robert Keehn says:

My favorite function…now that’s tough. Two that I use most frequently would be IF and VLOOKUP. If is incredibly useful when nested with other functions, so I would say the IF statement. I haven’t used it in an array, but I will read more to learn.

46. chunlee says:

My favourite excel function is index and match and choose.

47. Ben says:

When it comes to my favorite functions that I like to combine with arrays, it’s AVERAGE. I frequently have to calculate the simple average year-over-year changes for various things like sales, share prices, etc. Arrays and AVERAGE make this task a breeze.

For example, suppose I have yearly sales data in cells A1 through A5. I can calculated the annual average percent increase with the following array formula:

{=average(A2:A5/A1:A4-1)}

48. Chris hicks says:

{=IF(ISBLANK(\$B\$8),”–“,IF(ROWS(\$F\$5:F5)<=\$G\$5,INDEX(SHEET2!\$G\$2:\$G\$50000,SMALL(IF(SHEET2!\$B\$2:\$B\$50000=\$B\$8,ROW(SHEET2!\$B\$2:\$B\$50000)-ROW(SHEET2!\$B\$2)+1),ROWS(\$F\$5:F5))),"–")) } is my favorite array formula. Amazing projects have been created using this awesome array formula. I learned this from ExcelisFun's YouTube channel two years ago.

49. elaine says:

Like many others my favorite is vlookup. I use it quite extensively at work. I have tried IF function in array formula but that was my first attempt after watching online videos. If vlookup was intimating ten years ago, I think array formula is the same. Long way to go!!

50. Thuy says:

Vlookup is what got me hooked onto Excel and second to that would be if, else function. Thanks for having this contest!

51. Jayson says:

My favorite is the one that gets the job done :)

Usually involves index/match/vlookup or any combination.

52. Lillianne says:

I too am a fan of SUMPRODUCT and have used it many times as an array formula when I need to sum or multiply values with multiple criteria.

53. Anar says:

I used VLOOKUP many-many times both as a function and in array formulas. Remember an instance when used IF and VLOOKUP functions in array formula to compare two lists.

54. Maggie Wang says:

Wow, so many amazing formulae in the comments left above. Thank you for sharing your knowledge :-). My favorite is “index/match” which I use it on a daily basis to look up values and do other things; this followed by Sumproduct.

55. Al says:

Hi!

Here’s a cute one I just started using at my job.
=IF(B130=”1″,Q134,IF(B130=”(All)”,Q133,”Something is wrong. Sorry about that”))

The backstory: boss asked to create a pivot table with slicers/filters he can play with, and insisted there would be only one chart and that the title of the chart must change according to the filters.
So, what I did was to link the chart’s title to a cell, which, using the simple if function above, changes it’s values according to the value of the filter of the pivot table (in b130). Possible titles are in Q133, Q134.

This is of course the simplest case. I’ve got other sheets with quite a few combinations :)

Another one I use is:
=DAY(NOW())/DAY(EOMONTH(NOW(),0))

when I’m asked how are we doing Vs. predefined goal of the month (for example, leads generated, Sales \$), I compare the actual numbers with the MTD linear goal, which is the goal for the month, times the % of month passed so far, as given in the formula.

Thanks! :)

56. Chris U says:

My favorite is the index/match combination when looking up on multiple criteria in the row and/or column which requires the array formula.

57. Zach LEBOVITZ says:

It was a warm Southern California summer school afternoon and the mood was bleak. Will our instructor let us out early? Has anyone ever died in a statistics course? How did I get here? As these were being pondered, the teacher had a question about multiple criteria in a formula….. The elusive “light bulb” moment occurred and I jumped into action. I told the instructor about SUMIFS and explained its benefits. The smile on his face was nice, but as a reward, he let the class out early! I became a summer school legend….for one day.

I love excel

• Dan says:

You rock, Zach! People never forget early dismissals.

58. Jason M says:

Mastering VLOOKUP was my entry into the world of Excel and I still use it frequently, so it has a special place in my Excel bag of tricks. Have I ever used it an array function? Actually, I did use it to look up and then multiply 3 data points one time.

=PRODUCT(VLOOKUP(H1,A:E,{2,3,5},0))

59. Jason M says:

Array-entered of course!

60. C.A. West says:

I use SUMPRODCUT with structured references quite often, as well as INDEX and MATCH.

61. Gonzalo says:

Of course, my favorite is the index/match combination. For example, {=SUMA((\$B16>=INDICE(DataBaseFilter;;5))*(INDICE(DataBaseFilter;;5)>0))}

62. Alan says:

It’s hard to say which one is my favorite, but I couldn’t live without =IFERROR(GETPIVOTDATA(Blah, Blah, Blah),”0.0″). I must have thousands of cells with formulas that start this way. Never used it in an Array formula because Array formulas baffle me. That’s why I need the book. ;-) Thanks, Love your blog! (Brownie points)

63. Jennifer David says:

I recently discovered OFFSET from Chandoo’s website. I use it all the time to transfer data from one report to another and it has saved me days and days worth of time. I haven’t tried array formulas but would love to add them to my toolbag. I have Mr. Jelen’s Excel 2007 book and have learned so much from it. It would be great to have another one of his books. Thank you.

64. Dan says:

I probably do too much (but still not enough) in VBA rather than in formulas. Because of my work in financial services, I use the financial formulas but the function that is most helpful, most often is vlookup. I should be the hands-down winner of the book because I have just short of no knowledge of how to use arrays. Am I in a 1995 Excel time warp??

65. Andrew Sinha says:

Not really a function but a number of key strokes to fill in the blanks with the value above. Used to be invaluable with pivot table before the new 2010 incarnation solved it.
CTRLg (to open the go to dialog)
ALTs (to open go to special)
K (to select blanKs)
Enter (to complete the selection of blanks)
click the up arrow (to select the cell you want to copy)
then click CTRL Enter (together)
This will now fill in the blamks with the details of the cell above it. Cheers

I required to use arrays as the way to count rows filled and make dynamic tables base on the data present on the sheet.

67. Robert Salonikios says:

Hello,
I would like to say that less than a couple of years ago, I was just a novice in excel. I had an interest in excel formulae as this was invaluable to my line of work. Today, I am writing advanced formulae and macros (VBA). I admit that I owe a great deal to the web-site of Mr Bill Jelen.
I am very interested to acquire an in-depth knowledge in array formulae.
My favourite excel formula is the vlookup (and the index – match).
Most of the above comments are really impressive but as I said earlier I am gradually mastering the fundamental essentials of excel and I strongly believe that if I am lucky enough to win the text “Mastering Excel Array Formulas”, then I would have the opportunity to learn one more fascinating aspect of excel.
I wish everyone good luck and may the winner know ahead in time that he/she would be really lucky to get his/her hand on such invaluable resource.

68. John A Robinson says:

Sumifs is the formula I most often use. Thou SumProduct has a lot of promise. I have limited experience with array formulas, mainly to do data analysis and want the results in one place.

69. David Unger says:

I would have to say Index/Match is my favorite function combo, followed closely by Offset. Thanks to information provided by others, it’s amazing what can be accomplished with these. I’m slowly learning to use formulas in place of VBA (when applicable), but I must admit that array formulas are still a bit of a mystery to me – this book would be SO helpful!

70. Virendra says:

Functions that I’ve found pretty useful are the text and indirect. I work in an environment where people are fond of inserting rows and copying formatted excel data elsewhere and wondering what’s going on.

The indirect and text have benefited me.

71. Probably my most used formulas are LEFT and RIGHT. In doing data analysis where users input data in some many different “right” ways, these really come in handy. Though I will admit the INDEX/MATCH trick is very useful. I don’t know much about array formulas, however, and therefore haven’t used these functions with them.

72. Opie Velagandula says:

My favorite excel function is VLOOKUP. I use it almost in every one of my projects to perform gas turbine operation based life data analysis.

73. Jim Welsh says:

One of my favorites is to take all the files in a folder and then merge certain ranges into one sheet. I use the Function RDB_Last(choice As Integer, rng As Range)by Ron DeBruin to add the data from each workbook below the data from the previous one. Once you get the hang of it, it really saves time.

74. I use many of the functions already mentioned although seldom use array formulae, My colleagues believe I can type in three languages one being Excel. My arguments/formulae tend to require a large screen to display. I love the offset function which allows me to use a named range to create graphs which grow and display data without having to adjust the ranges everytime. It takes a huge burden off reporting on a regular basis as I have to so

OFFSET(Sheet!\$A\$1,1,0,COUNTA(\$A:\$A)-1) for all records in the array in column A or
OFFSET(Sheet!\$A\$1,COUNTA(\$A:A\$)-52,0,52) for the last 52 weeks of data in a growing array in column A.

I have been known to use {MATCH(True,(A1:z1)””,0)} to find the left most non blank cell in an array. I think Mr Jalen helped with that solution on his great website.
With worked examples for the learners like myself this book could prove invaluable. PS I am an old dog learning new EXCEL tricks everyday!!!

75. Sumit says:

Apart from using array formulas such as Index/Match, Sumproduct, Vlookup I recently used array in formulas such as Search/find (which are not made to handle arrays as such) to look for multiple strings in a range at the same time. Supercool stuff!!

76. Orlando Silva says:

Definitely this is my favourite and often used one.
With this array function I can create a list of unique values from a large and very often repeated values. And better of all… it works fine. :)

{=IFERROR(INDEX(AU117:AU126;SMALL(IF(MATCH(AU117:AU126;AU117:AU126;0)=ROW(INDIRECT(“1:”&ROWS(AU117:AU126)));MATCH(AU117:AU126;AU117:AU126;0);””);ROW(INDIRECT(“1:”&ROWS(AU117:AU126)))));””)}

77. Ryan Blazei says:

Before PowerPivot it would have definitely been SUMPRODUCT() which was quite difficult to explain to co-workers at times. Now it has to be using TEXT() and INDIRECT() to make my CUBE functions dynamic when reporting against cubes.

78. Sara says:

I don’t have any one particular favorite, although I tend to gravitate towards SUMIF, GETPIVOTDATA, and the old standby VLOOKUP. I haven’t done any of those as an array, but I was pretty tickled when I figured out I could do PERCENTILE in an array formula AND make it dynamic using named ranges with offset formulas.

79. Gina says:

My favorites are VLOOKUP and SUMIFS. Haven’t used arrays in a while, but need to refresh myself on those!

80. Debra Dalgleish says:

Thanks for your entries! The deadline has passed, and the winners will be announced tomorrow.

81. Dexter says:

Mine is quotient + mod. It can compute other number system, eg. Base 12, 24 and 30 etc.

82. John Edward says: