Software Search, Categories and Specials
Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Active DataXL - Download

 

AnalyserXL - Download

 

DownloaderXL - Download

 

Smart VBA - Download

 

TraderXL - Download

Excel Tips and Tricks

Microsoft Excel - From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

 

Alternatives to Excel Array Formulas

Excel Tips & Tricks   Start

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.

3) Transposing data using regular formulas

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.

More Excel Tips & Tricks

Excel Tips & Tricks Start & Find Your Excel VBA Procedure Quickly From Your Workbook Window & Alternatives to Excel Array Formulas & Sumproduct & Other Useful Regular Excel Formulas & Advanced Filter & Other Tips

Excel Tips & Tricks  $19.95 | Secure Payment Options | Instant Buy/Download | 30 Day Money Back Guarantee Developers Site | More Tutorials

Special! Free Choice of Complete Excel Training Course OR OzGrid Add-ins Collection on ALL purchases totaling over $70.00.
ALL purchases totaling over $150.00 gets you BOTH!  Purchase(s) MUST be made via this site.


Instant Download and Money Back Guarantee on Most Software

Microsoft Excel - From Beginner to Expert in 6 Hours

Microsoft Excel - From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft