VBA - Calculate Percentage Values

[Ananthak275]

Got a table that starts on B5 and C5. I want to get the following calculations:

	Latvia (b5)	Russia (c5)
Lacrosse	2	5
Hockey		5
Basketball	1
Football	1
Soccer	1	1

i want to calculate the following:
1. Percentage of total overlap of sports played in both Countries, each row gets 1 value is played in both sports, dived by the total number of sports (in this example 5). So this one would be Lacrosse and Soccer which is two sports, hence output would be 2/5 = 40%. Output should be in cell A1. Just the percentage in a1.
2. how many are played ONLY in Latvia. In this example: Basketball and Football so 2/5 = 40%. Output should be in cell B1. Just the percentage in B1.
3. how many are played ONLY in Russia. In this example: Hockey so 1/5 = 10%. Output should be in cell C1. Just the percentage in c1.

 

[StephenCrump]

Like this perhaps ...

	A	B	C	D	E
1	40%	40%	20%
2	Both	Latvia	Russia
3
4
5			Latvia	Russia
6		Lacrosse	2	5
7		Hockey		5
8		Basketball	1
9		Football	1
10		Soccer	1	1
11
Sheet1

Cell Formulas
Range	Formula
A1	A1	=1-SUM(B1:C1)
B1	B1	=SUMPRODUCT(ISNUMBER(C6:C10)*ISBLANK(D6:D10))/ROWS(B6:B10)
C1	C1	=SUMPRODUCT(ISNUMBER(D6:D10)*ISBLANK(C6:C10))/ROWS(B6:B10)

But I'd do it differently if you were scaling up to more than two countries?

 

[Ananthak275]

Like this perhaps ...

	A	B	C	D	E
1	40%	40%	20%
2	Both	Latvia	Russia
3
4
5			Latvia	Russia
6		Lacrosse	2	5
7		Hockey		5
8		Basketball	1
9		Football	1
10		Soccer	1	1
11
Sheet1

Cell Formulas
Range	Formula
A1	A1	=1-SUM(B1:C1)
B1	B1	=SUMPRODUCT(ISNUMBER(C6:C10)*ISBLANK(D6:D10))/ROWS(B6:B10)
C1	C1	=SUMPRODUCT(ISNUMBER(D6:D10)*ISBLANK(C6:C10))/ROWS(B6:B10)

But I'd do it differently if you were scaling up to more than two countries?

Hi,

I considered doing it through formulas but I need VBA script. Sorry

 

[StephenCrump]

I considered doing it through formulas but I need VBA script. Sorry

Sorry from me too. I read the question too quickly and didn't see VBA in the header.

Why do you need VBA for such a trivial example? On re-reading properly, this looks to me like an assignment question?