Calculating a percentage of bounced emails per company

[imaguy77]

Hello everyone,

I have a question...it seems like it might be kinda tricky.


Here are my field names:

LastName, FirstName, Company, True/False


So, I have a list of contacts at various companies, and under the T/F field I have FALSE if the person NO LONGER works there and TRUE if the person DOES work there. I'm trying to delete contacts in my database according to the percentage of people that no longer work there.

So, what I want to calculate is for each UNIQUE company name, whether ALL the contacts return FALSE in the T/F field (i.e. none of my contacts are correct), 50% or less return FALSE or more than 50% return FALSE.

How can this be achieved???

The formula =COUNTIF($B$2:$B$844,B2)

will say how many contacts I have for each company, and it works SO LONG AS 2 adjacent companies do not have the SAME number of contacts.

once I have the # of contacts for each company, I need to sum up the TRUE/FALSE values and divide by the total in order to obtain a % of people still there. But how do I take the number of contacts for the company, perhaps obtained by the COUNTIF function, and then search THAT NUMBER of places down in the T/F field in order to get the correct number to divide into the total?

Thanks for your help!!!!

 

[imaguy77]

sorry...i titled this question wrong...

the way i obtained the True/False was by bounced emails...but that's not really important for my question, so just assume that the T/F are just hard coded in there.

 

[Gerald Higgins]

Have you considered a pivot table for this ?
This could quickly tell you how many true and false entries you had for each company.
For example
.........TRUE...FALSE
Co.A...3........2...
Co.B...0........5...
Co.C...7........0...
and so on

 

[imaguy77]

ah.....pivot table. i've never used one to actually do anything...just watched a video on them a little while back. okay, that should work though. a lot easier probably. thanks!

 

[imaguy77]

Okay, so I used the pivot table to summarize the number of T/F for each company, and a grand total for the total number of contacts for each company.

My question now is, how can i do math on the calculated values? I want to get a percentage, by dividing the number of TRUE by the Grand Total. However, Excel isn't really letting me just do a formula using the values calculated by the PivotTable because when i drag the formula down, it doesn't do relative cell references.

Thanks.

 

[charlie1105]

Ok, if you have the company as the row field, and T/F as the column field, then add T/F to the data field too.

Right click on the "count of t/f" and goto field settings. Select options, then "Show data as % of row"

This should show you what you need.

Spend some time playing with pivot tables, in my view they are Excels most powerful feature!

 

[charlie1105]

Alternatively, you can use your method, but when writing your forumla, rather than clicking the cells in the pivot table to reference them actually type the cell references.

You can then drag this down

E.g. type "=B5/D5" and drag down