Calculating Percentage of Data in Column

**jlearned** · 10-20-2007, 06:20 PM

I am putting together a Service Delivery Report fro the Fire Department I work for. I am tracking the amount of time it is taking units to respond and arrive at calls. The amount of time in minutes is then compared against the departments time response standard. I am using an IF function to do this for example, Dispatch time is 10:00:00 arrival time is 10:05:00 so the response time is 5 minutes. I then take and use the IF/Then function and state that if < 10 minutes then 1 if not then 2. What I need to do is figure out the percentage of 1's there are in relation to the total number of calls.

Thanks

-Jason

**protonLeah** · 10-20-2007, 07:08 PM

Say you use column B for dispatch time, column C for arrival time and col. D for your IF statements; then, in col E you could count the total number of dispatches with the formula:

Please Login or Register  to view this content.

**shg** · 10-20-2007, 07:40 PM

Suppose you have headers in the first row (A1 = "Alarm", B1 = "Response"), and data below that as Excel dates. Then define these names (Insert > Name > Define):

Tbl =OFFSET(Sheet1!$A$1, 1, 0, COUNTA(Sheet1!$A:$A)-1, 2)
Alarm =INDEX(Tbl, 0, 1)
Response =INDEX(Tbl, 0, 2)

Then this formula will show the percent of response times meeting the criterion:

=SUMPRODUCT( --(Response - Alarm <= TIME(0,10,0) ) ) / COUNTA(Alarm)

Because this formula references cells formatted as time, Excel will automatically format it as time. Just change the formatting to %.

Calculating Percentage of Data in Column

LinkBack

Thread Tools

Rate This Thread

Display

Calculating Percentage of Data in Column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions