Formula or macro to analyze data vertical and horizontal?
 

Hello:
This is referencing Excel 2007.

I am using a formula to be able to identify if a particular ID is "Approved
1st time". Where it gets tricky is that the data needs to be analyzed at the
ID level and sometimes there are multiple lines referencing the same ID.

In the example below, ALL lines that reference the same ID would need to
meet the criteria in order to be considered "approved 1st time".
Criteria for approved 1st time is as follows:
- Date field must be blank - AND-
- Eval status contains *Approved*

Based on above criteria, only ID#PL0698 would qualify. But I only want it to
count as 1, not 2 in the total analysis of "approved 1st time".

In addition, I need a total count of how many discrete IDs there are so that
I can show the percentage that were approved 1st time. In this example, 1 of
2 or 50% Approved 1st time.

Category ID# Date Eval Status
Size PL062 Rejected by HQ
Quality PL062 Approved by HQ
Design PL062 Approved by BP
Size PL0698 Approved by HQ
Design PL0698 Approved by HQ

Thank you!

Formula or macro to analyze data vertical and horizontal?
 

Suppose your ID numbers are in B2:B8, dates in C2:C8, and status in
D2:D8. Use the following formula to find the number of elements that
have ID = 1234, a non-empty date value, and the word "approved" in the
somewhere in the status text (e.g., "approved", "finally approved",
etc).

=SUMPRODUCT(--(B2:B8=1234),--(C2:C8<""),--(NOT(ISERROR(FIND("approved",D2:D8)))))

To get the number of distinct IDs, use the following array formula:

=SUM(1/COUNTIF(B2:B8,B2:B8))

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Mon, 15 Mar 2010 13:04:01 -0700, Roady
wrote:

Hello:
This is referencing Excel 2007.

I am using a formula to be able to identify if a particular ID is "Approved
1st time". Where it gets tricky is that the data needs to be analyzed at the
ID level and sometimes there are multiple lines referencing the same ID.

In the example below, ALL lines that reference the same ID would need to
meet the criteria in order to be considered "approved 1st time".
Criteria for approved 1st time is as follows:
- Date field must be blank - AND-
- Eval status contains *Approved*

Based on above criteria, only ID#PL0698 would qualify. But I only want it to
count as 1, not 2 in the total analysis of "approved 1st time".

In addition, I need a total count of how many discrete IDs there are so that
I can show the percentage that were approved 1st time. In this example, 1 of
2 or 50% Approved 1st time.

Category ID# Date Eval Status
Size PL062 Rejected by HQ
Quality PL062 Approved by HQ
Design PL062 Approved by BP
Size PL0698 Approved by HQ
Design PL0698 Approved by HQ

Thank you!