Daily posts of Excel tips…and other stuff
Archive for the ‘Array formulas’ Category.
Let’s say you have some data like, oh I don’t know, First Name, Last Name, Gender, State, and some number.
You could add up the numbers for all the males in Texas with an array formula.
=SUM((C2:C101=”Male”)*(D2:D101=”Tx”)*(E2:E101))
If you didn’t know that, go read Anatomy of an Array Formula. One of my workshop attendees had [...]
Tom asks:
I have three values with wind direction at differrent altitudes.
A1: 020, A2: 2000 feet,
B1: 010, B2: 5000 feet,
C1: 350, C2: 10000 feet
and I would like to forecast the wind direction for D2: 3000 feet. I’ve tried the forecast function but it goes haywire with the change from 010 to 350 degrees.
I made [...]
I need help. I have this:
I can tell my how many unique fruits are in column A with this array formula
E4: =SUM(1/COUNTIF(A2:A13,A2:A13))
And I can tell how many rows have both Apple in column A and 1 in column B with this array formula
E5: =SUM((A2:A13=”Apple”)*(B2:B13=1))
But I can’t seem to figure out the formula to tell [...]
Created by David Hager, Bob Umlas and Laurent Longre
The problem - to create an array containing only the unique items from an expanding column list. In other words, if items are typed down column A, what is the formula that will return the unique items? The following example further illustrates the problem.
In this case, the [...]
Here's what my (partially obfuscated) desktop looks like:
I put information I need on my desktop so I'm a Windows+M key combination away from what I need. The Excel file sits in my XLStart directory so the calendar gets updated every day.
VBA from Excel to Desktop
In the Workbook_Open event, I have
Private Sub Workbook_Open()
[...]
