Need help with Excel 2003 creating a formula

Microsoft Excel 2003 (full)
March 25, 2010 at 06:37:00
Specs: Windows XP
I have ~40,000 rows of data, and 15 colums of data. Column B lists city name and column M list ambulance response time (as a whole number). The worksheet data is sorted by city name, then by response time so that in B, each city listed alphabetically may appear 5,000 times in a row. The corresponding ambulance response time in M appears from shortest response time to longest. I want to have excel search column B for the city name I identify, and then search the corresponding rows in column M for all response times less than 11 minutes and tell me how many there are.

This way all I need to do is use this formula and change the city name to have the calculation done for each city.

Thanks!


See More: Need help with Excel 2003 creating a formula

Report •


#1
March 25, 2010 at 07:03:47
Create a dropdown list using a list of unique city names.

(Data...Filter...Advanced Filter can help you create the list)

Put this Drop Down wherever you like...I'll use P1.

In Q1, enter this formula and then hit Ctrl-Shift-Enter to create an Array Formula. Excel will place { } around the formula. Do not simply type the { } as that will not create an array formula.

=SUM((B1:B40000=C1)*(M1:M40000<11))

Each time you edit the formula you must use Ctrl-Shift-Enter to retain the array functionality.


Report •

#2
March 25, 2010 at 07:07:26
Hi,

If your data is in Sheet1 (column B city names and column M times), and
On Sheet2 in Cell A2 is the City name you want to use,
enter this formula in cell B2 on Sheet2:

=SUMPRODUCT((Sheet1!B2:B54=Sheet2!A2)*(Sheet1!M2:M54<11))

This will return the number of responses less than 11 minutes in the city identified in A2.

You will need to change the ranges to match the data - both the sheet names and the data range - I didn't have 50,000 rows of data!

You probably also want the number of entries for each city, so on sheet2 in cell C2 enter this:

=COUNTIF(Sheet1!B2:B54,A2)

Now in Cell D2 this:
=B2/C2*100
returns the percentage of responses less than 11 minutes for the selected City.

If you place the time in cell A3, then change the formula in B2 to:

=SUMPRODUCT((Sheet1!B2:B54=Sheet2!A2)*(Sheet1!M2:M54<A3))
Now you can easily change the response time as well as the city name.

Regards


Report •

#3
March 25, 2010 at 07:19:56
DerbyDad03

That didn't do anything. I created the drop down list, then created the array using that formula, but it just says 0. Also, what is the point of creating the drop down list, it doesnt seem to have anay effect on the array formula. Perhaps I am confused, what is the purpose of the C1 in the formula?

=SUM((B1:B40000=C1)*(M1:M40000<11))


Report •

Related Solutions

#4
March 25, 2010 at 07:29:53
Humar,

"You will need to change the ranges to match the data - both the sheet names and the data range - I didn't have 50,000 rows of data!"

This is the problem, I don't want to have to manually change the ranges, I want Excel to look at column B for the city name I identify, then automatically look at the same rows in column M. Otherwise I have to manually enter the row ranges for 48 cities and then if I need to delete one row, then have to go back to every single formula and manually change the row ranges. This is what I was doing before and it is way to tedious and time consuming.


Report •

#5
March 25, 2010 at 08:05:30
Hi,

You only change the range once when you enter the formula.

The range covers all your data - all cities - all 40,000 or so rows.

The formulas will identify all the rows with any selected city name.

I used B2:B54 and M2:M54 only because I was testing the formulas on a small amount of data. Those 53 rows included 10 different names.

You will enter something like B2:B45000 and M2:M45000.

If the range entered extends beyond your last used row, you will be able to add extra data below the last entry without having to change the formulas. If you add rows inside the existing range the formulas will automatically increase to accommodate the extra rows.

If you delete one row, the ranges in the formulas will automatically reduce by one - you will not need to change them manually.

Just do it once!

Regards


Report •

#6
March 25, 2010 at 09:24:25
Humar,

Still doesn't work (I get 0 as an answer). I got it to work using this sytnax:

=SUMPRODUCT((Sheet1!B2:B54=Sheet1!B2)*(Sheet1!M2:M54<11))

But this doesn't check against the city name on sheet 2 in cell A2 and I believe if I added or deleted lines on sheet 1 that affected what city name appeared in B2 it would mess up the calculation for the city I want.


Report •

#7
March 25, 2010 at 09:24:25
Humar,

Still doesn't work (I get 0 as an answer). I got it to work using this sytnax:

=SUMPRODUCT((Sheet1!B2:B54=Sheet1!B2)*(Sheet1!M2:M54<11))

But this doesn't check against the city name on sheet 2 in cell A2 and I believe if I added or deleted lines on sheet 1 that affected what city name appeared in B2 it would mess up the calculation for the city I want.


Report •

#8
March 25, 2010 at 09:47:49
Got it to work!!!

Thanks!!


Report •

#9
March 25, 2010 at 09:58:40
Hi,

Out of interest - what did you do to get it to work.


Regards


Report •

#10
March 25, 2010 at 11:35:14
Turns out the city name in the export from the state database I access places some funky hidden value before the city name in the cell. For example, if the city name is Brooklyn and that is in cell A2, it actually looks like there is a space in front of it but it is actually some hidden value. So I simply had to do a paste special (value) to create my city list that the formula you showed me accessed.

Report •

#11
March 25, 2010 at 11:53:31
re: what is the point of creating the drop down list

The drop down list is so you don't have to manually enter the name of the city. By using the "Advanced Filter...Unique values" you should get a drop down of exact matches so that typos won't impact the results of the formula.

re: what is the purpose of the C1 in the formula?

My error. I tested my formula using C1 and then suggested putting the drop down in P1 so it wouldn't be in the middle of your 15 columns of data.

The array fromula should have read:

=SUM((B1:B40000=P1)*(M1:M40000<11))

Ctrl-Shift-Enter

This will compare the values in B1:B4000 to the choice from the Drop Down.


Report •


Ask Question