If you find Excel array formulas powerful yet intimidating, the good news is that in many cases, you can convert your array formula into a regular formula and have the best of both worlds. We provide ten such examples (single formulas).

1) Cross tab table

You may think that this kind of table can only be built using only an array formula. With a little ingenuity you can get the job done using an amazingly simple regular formula.

2) Rank, running sum, sort by group (9 formulas)

Ranking by group, running summing by group, and sorting by group is tricky.

This screen shot demonstrates eight regular formulas and one array formula.

Those regular formulas substitute their array counterparts.

Note that the list is sorted by region.

Formula # 1: Assigns sequential numbers to items within each region, using one function.

Formula # 2: Calculates running sum by region, using one function.

Formula # 3: Ranks items within each region, using one function.

Formula # 4: Sorts items within each region, using three functions.

Formula # 5: Sorts the overall list, using three functions.

Formula # 6: Reverses values in the Sales column, using three functions.

Formula # 7: Finds the top sales figure within each region using two functions.

Formula # 8: Finds the top three sales figures within each region using two functions.

Formula # 9: Calculates an average sales figure for each region and finds sales above the average within each region, using one function.

Note: You can use Columns J, K, L as your AutoFilter criteria to get respective information.

This screen shot shows you how you can transpose your data using a regular formula. The formula involves three built-in functions. You can move around the transposed range without affecting the data.

4) A two-column lookup

This screen shot shows how to use a regular formula to look up something based on values in two columns.

5) How many matches between two single column lists?

You normally would use this array formula:  {=COUNT(MATCH(Rng1,Rng2,0))}

However, we have a non-array solution.

6) How do I count the number of unique items in a range?

7) You want to calculate the average of sales of your top 3 salespersons in a single formula without using an array formula.

8) How do I get the sum of serial numbers 1 through 100?

9) What would be the cumulative principal payments for the first threes years on a 30 year $200,000 loan with a 6 % annual interest rate?

10) How many Fridays are there between 1/1/06 and 3/31/06?

11) Is a particular value in a range?

12) You have sales data by salesperson for two months and want to calculate the average of the changes in a single formula.

13) How do I calculate the sum of an integer in a cell?14) Summing top three values in a range

15) Sum every second value in a range

16) Count the occurrences of a particular weekday in a given month of a given year.