How to build macro to do calculations by grouping column A by name

[J.J.]

Within this worksheet, I have 4 columns

Company Name #logs Boardmember
ABC Luis 1 yes
ABC Anne 1 yes
ABC Mary 9 yes
XYZ John 1 yes
XYZ Grace 4 no
XYZ Ben 8 yes
345 Erin 2 no
345 Kate 0 no
345 Greg 0 no

What I want to solve for is-
Board member Usage (#boardmembers with logs per company>1/
#boardmembers per company)
Non Board member usage (#nonboardmembers with logs>1 per company/
#boardmembers per company)

For example,
Usage
---------
Team ABC usage = 33%
Team XYZ usage = 66%
Team 345 usage = 100%

Is there a way to do this in a macro? My main problem is finding a way
calculate by company name. Any help would be great

 

[Mike H]

Hi,

Your pseudo formula is

nonboardmembers with logs>1 per company/boardmembers per company

so for ABC

ABC Luis 1 yes
ABC Anne 1 yes
ABC Mary 9 yes

3 board members with 1 greater than 1 so your answer of 33% seems to agree
with your pseudo formula

However, Group XYZ

XYZ John 1 yes
XYZ Grace 4 no
XYZ Ben 8 yes

evaluates as 1/2= 50% according to your pseudo code.

or if you mean NON board members
1/1=100%

I can't get 66% out of this

please clarify

Mike

 

[J.J.]

Hi Mike, thanks for the reply!

Please let me fix that as it was a mistake.

boardmember usage
------------
Team ABC boardmember usage = 33% (1/3) ;
Team XYZ boardmember usage = 50% (1/2);
Team 345 boardmember usage = undefined (0/0)

Team ABC non boardmember usage = undefined (0/0) ;
Team XYZ non boardmember usage = 100% (1/1);
Team 345 non boardmember usage = 100% (1/1)

I hope this makes sense and that I didn't make another mistake on
this. Please let me know if there is anything else that I need to
explain. Thanks!