I posted a several question time ago, then I abandoned the problem,
now I had to go back to it again

Sheet 1:
I have a big table with 100 countries in column A, and many columns of
different variables in the other columns.

Sheet 2:
I create several clusters of countries, for example:
Column A: Cluster 1= country1, country 3, country 88
Column C: Cluster 2= country 3, country 66
Column E: Cluster 3 = country 4, country19, country12, country77,
country 33, country 78, country 99

For each cluster, I do have (in column B, D and F) a percentage
(weight) close to each country. That means that country 3, belonging
to 2 different clusters, will have two different weights.

I am looking for a formula that allows me to do the following:

Find (in the big table in Sheet 1) the countries belonging to Cluster
1, then multiply the corresponding weight (column B for cluster 1 in
sheet 2) with the number found in column P (sheet 1) for the same
country, and sum it to the same product for the other countries in the
cluster.
In a nutshell, is a weighted average of a single column in Sheet 1 (P
in my example) only for the countries belonging to the specified
cluster, and using the specific weights for that cluster.

(IF A country belongs to Cluster 1, then multiply column P with the
weight for this country on Cluster 1, and add with the same thing for
the other countries of the cluster)

Hope it is clear…
my objective is to add the clusters as fictitious countries at the
bottom of my database on Sheet1, but in such a way that I can change
the clusters in a quick way.

Time ago somebody (daddylonglegs) had suggested for my problem at that
time
=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100,cluster,0)),B2:B100,C2:C100 )

where A, B and C belong to Sheet 1. But now My column C is not in
sheet 1 anymore, and that array (weights) doesn't have the same
dimension. I know I could always add it at the end of my big table in
Sheet 1, one new column for each different cluster

And also I do not understand how does the Match function works he
if I dig into it and isolate it, it shows an error, but the overall
nested formula works. And also I dont know the "--" role...

thanks in advance

=SUMPRODUCT(ISNUMBER(MATCH(A2:A100,Cluster,FALSE)) *B2:B100*P2:P100)/SUMPRODUCT(ISNUMBER(MATCH(A2:A100,Cluster,FALSE))* B2:B100)

HTH,
Bernie
MS Excel MVP


"l" wrote in message ...
I posted a several question time ago, then I abandoned the problem,
now I had to go back to it again

Sheet 1:
I have a big table with 100 countries in column A, and many columns of
different variables in the other columns.

Sheet 2:
I create several clusters of countries, for example:
Column A: Cluster 1= country1, country 3, country 88
Column C: Cluster 2= country 3, country 66
Column E: Cluster 3 = country 4, country19, country12, country77,
country 33, country 78, country 99

For each cluster, I do have (in column B, D and F) a percentage
(weight) close to each country. That means that country 3, belonging
to 2 different clusters, will have two different weights.

I am looking for a formula that allows me to do the following:

Find (in the big table in Sheet 1) the countries belonging to Cluster
1, then multiply the corresponding weight (column B for cluster 1 in
sheet 2) with the number found in column P (sheet 1) for the same
country, and sum it to the same product for the other countries in the
cluster.
In a nutshell, is a weighted average of a single column in Sheet 1 (P
in my example) only for the countries belonging to the specified
cluster, and using the specific weights for that cluster.

(IF A country belongs to Cluster 1, then multiply column P with the
weight for this country on Cluster 1, and add with the same thing for
the other countries of the cluster)

Hope it is clear.
my objective is to add the clusters as fictitious countries at the
bottom of my database on Sheet1, but in such a way that I can change
the clusters in a quick way.

Time ago somebody (daddylonglegs) had suggested for my problem at that
time
=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100,cluster,0)),B2:B100,C2:C100 )

where A, B and C belong to Sheet 1. But now My column C is not in
sheet 1 anymore, and that array (weights) doesn't have the same
dimension. I know I could always add it at the end of my big table in
Sheet 1, one new column for each different cluster

And also I do not understand how does the Match function works he
if I dig into it and isolate it, it shows an error, but the overall
nested formula works. And also I dont know the "--" role...

thanks in advance