State Level Dynamic Count

[lizardbreath]

Hello,

I will do my best to explain what I am looking to do.

1) Column A has a person's name
2) The top columns have all 50 states
3) The states that the people have licenses in will have an "x" in them.

So it would look something like this

[TABLE="width: 435"]
<tbody>[TR]
[TD][/TD]
[TD]CA[/TD]
[TD]AZ[/TD]
[TD]FL[/TD]
[TD]OR[/TD]
[TD]NV[/TD]
[/TR]
[TR]
[TD]Betty Sue[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Alert Einstein[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Bush[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weighted Total[/TD]
[TD="align: right"]1.58[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.33[/TD]
[TD][/TD]
[TD="align: right"]0.33[/TD]
[/TR]
</tbody>[/TABLE]


What I am trying to do is derive an excel calculation (Or VBA function) that looks at all of the licenses that have an "x" in them for that 1 person. Then it takes 1 and divides it by the number of licenses with the X and that assigns a value for each of the licenses for that person.

As an example looking at Betty Sue in the image above. She has 3 licenses in CA, FL, and NV. If we took her 1 and divide it by 3 (for her 3 licenses), each of the states are given a weight of .33

For Albert Einstein he has an X in CA & AZ. So if you 1 and divide it by his two states, it gives them both a .5 weight. So CA & AZ would be allocated a weight of .5

Is this possible in excel?

 

[Michael M]

wouldn't that suggest then that Betty Sue has more licences, but gets weighted lower than Albert ??
And George Bush would be even Higher !!
Having said that

Excel 2007
	A	B	C	D	E	F	G	H
1	Name	CA	AZ	FL	OR	NV
2	Betty		x	x	x		0.333333
3	Albert	x	x				0.5
4	George	x					1
5
Sheet1

Cell Formulas
Range	Formula
G2	=1/COUNTIF(B2:F2,"x")
G3	=1/COUNTIF(B3:F3,"x")
G4	=1/COUNTIF(B4:F4,"x")

 

[Peter_SSs]

Assuming that the "X" values are entered manually (that is, not the result of a formula), is this what you mean?
H2 copied down, B5 copied across.

Excel Workbook
	A	B	C	D	E	F	G	H
1		CA	AZ	FL	OR	NV
2	Betty Sue	X		X		X		0.33333
3	Alert Einstein	X	X					0.5
4	George Bush	X						1
5		1.83	0.5	0.33	0	0.33
State Count

 

[lizardbreath]

wouldn't that suggest then that Betty Sue has more licences, but gets weighted lower than Albert ??
And George Bush would be even Higher !!
Having said that
Excel 2007

	A	B	C	D	E	F	G	H
Name	CA	AZ	FL	OR	NV
Betty	x	x	x
Albert	x	x
George	x

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]0.333333[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G2[/TH]
[TD="align: left"]=1/COUNTIF(B2:F2,"x")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G3[/TH]
[TD="align: left"]=1/COUNTIF(B3:F3,"x")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G4[/TH]
[TD="align: left"]=1/COUNTIF(B4:F4,"x")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes that is correct. The way that this would be used is slightly different than most use cases. But basically we have caps hear. So when you have more licenses it means that the "cap" can be taken up by other licenses; making the license you have less effective.



I really appreciate the work from both of you, I will test these out and let you know how it goes!