AVERAGEIF with String Parsing

**sdoah206** · 07-14-2011, 12:39 PM

First time poster to Excel Forum, so I greatly appreciate your help.

I've been tasked with calculating the average duration of a task across multiple markets. The hard part is the data needed to complete the calculation is included in a cell with two other pieces of information. Trust me, I'd rather break the data into separate columns, but my manager won't let me.

So the task columns can be formatted in one of two ways:

Complete task = "c" {Date task was completed} (Duration)
Activate task = {Expected completion date}

Here's an example of the data (attachment might be easier to read):

Market Task_1 Task_2
A c 06/01/11 (42) c 6/1/11 (42)
B 07/25/11 c 06/21/11 (4)
C c 06/01/11 (42) c 06/01/11 (5)
D c 06/05/11 (4) 08/24/11

I thought I was close to completing the calculation using AVERAGEIF and MID, but I can't seem to get a working function. Any ideas?

Thanks,

James

**Miraun** · 07-14-2011, 01:29 PM

Hey James;

I'm having a little bit of difficulty understanding exactly what you're looking for based upon the information provided.

Which values are you looking to average? For Task 2, would it be the average of 42, 4, and 5, as those are the ones marked as completed? Or are you also looking to calculate the number of workdays from the estimated completion date until the current date?

Are the dates important at all?

C2 was dated as 6/1/11 where the other dates were listed as 8 digit strings, so I would have expected to see this as 06/01/11, similarly to cell C4... is that a common problem within the worksheet?

If your boss doesn't like the idea of separating out the data into separate columns, are you looking to do the entire parsing through and adding up in a single cell? Can you add additional columns?

**sdoah206** · 07-14-2011, 01:41 PM

Thanks for the response. Let me see if I can clarify.

I'm looking to average the duration for completed tasks only. So as you indicate below for task_2, it would be the average of 42, 4, 5.

I'm not concerned about calculating the number of work days from estimated completion to the current date.

The formatting of the dates is not important. They should all be 8 digits, so my apologies for the inconsistent example. Then again, I have a feeling users will not be consistent in how the dates are entered.

My boss wants the calculation completed in one field as there are about 30-40 columns of tasks already.

**Miraun** · 07-14-2011, 03:47 PM

Well, as an interim response, I've accomplished the if statements to obtain the numbers... but for some reason, I'm having problems when I integrate them into array statements. As long as the dates are formatted as 00/00/00, the following will work:

=IF(LEFT(C3,1)="c",IF(ISERROR(MID(C3,13,2)/1)=FALSE,MID(C3,13,2),MID(C3,13,1)),0)

Unfortunately, I'm leaving for the day, hopefully another forum member can leverage that equation towards making a sum and count array function.

**daddylonglegs** · 07-14-2011, 03:59 PM

Assuming duration is always in brackets you can use this array formula

=AVERAGE(IF(LEFT(C2:C10)="C",-MID(C2:C10,FIND("(",C2:C10),10)))

confirmed with CTRL+SHIFT+ENTER