All articles with 'array formulas' Tag

Cheryl asks via e-mail, “I was wondering if you could help me figure out how to combine the round formula with the rank formula? I need to first round all the numbers and then rank them.”

Of course we can solve this by simply using array formulas. Curious? Find out more by reading the rest of this post.

Excel has very powerful formulas and add-ins for performing almost any kind of statistical analysis. Today we will learn how you can make a statistical distribution of test scores using excel. There is a downloadable workbook too, for you to take a look at the formulas and play with them.

I am math-geek-wannabe, if there ever is such a category. During my 3rd year of graduation I went and purchased the volume 2 of Donald Knuth’s Art of Computer Programming and thus began my love with all things random and prime. I never really became the math-geek I always wanted to, instead I became an insurance expert with tons of passion for data and visualization. But when I get a chance to poke with randomness or numbers, I always lap it up with joy. And that brings us to an interesting array formula trick to check if a number is prime or not.

(assuming the number is in the cell B2) type the below formula and

=IF(MIN(MOD($B$5,ROW(INDIRECT(“2:”&INT(SQRT($B$5))))))=0,”not prime”,”prime”)

hit ctrl+shift+enter and bingo, it tells you if the number is prime or not. Read on to find how it works

In this week’s excel links, we will learn how to jump to next cell during data entry, an array formula trick to include all values conditionally, about auto-filters in excel and much more.

In today’s installment of “your week @ PHD” we will learn how to use array formulas to refine text search, a bunch of very useful keyboard shortcuts, and a very simple tip on how to get ready when you want to make a chart. All really juicy tips, so read on.

The first installment of “your week @ PHD” features 4 excel tips shared by our readers: (1) A macro to unhide all sheets (2) a KPI Dashboard with VBA and Charts (3) Using Excel’s Find to Save time at work (4) An Array Formula to Solve a Tricky Problem.

Jarad asks me in an email “how word frequency can be generated from a range of cells using excel formulas?” This got me thinking and lead to this post, where we learn how to calculate word frequency using array formulas and use it to analyze a bunch of search keywords.

In this installment of our spreadcheats series, we will learn how to use array formulas and findout if a list has any duplicate items. We will use Countif and array formulas to do this …