Hello all,
It is the first time I made an entry in this forum to ask for a bit a help, so far the existent threads have been very helpful to solve my problems and thank you for that. But this time, I fear that my problem may be to specific - or there is too much implications I am ignorant of - as I did not find a solution in existent threads in this website or others. So I hence tried to be to most concise with my title. I apologise in advance if it may sound a bit bizarre. So I'll try to explain my set up and what I want to do with the most explicitly.
So I have a data set of more or less 5000 entries. Each entry has five different attributes/dimensions but only one value. I cannot disclose my data due to diligence issues so I would use a generic example. Let's say I have the height (the value) of 5000 people, for each person I have his hair color, eye color, his nationality, the city he were born and the name of his mother (the 5 dimensions).
My goal was to compute the average height of people depending on the dimensions I wanted to include or not. So I could seek for example the average height of blue-eyed and blond-haired people or swiss brown-haired people whose mother is called Heidi. I thus created a 7th column in my data set that concatenate together only the desired dimensions. I would hence create different some sort of combinations and being able to recuperate their average weight. It looks like this:
The first entry is in row 5
In column G:
The AR2 to AV2 cells contains TRUE/FALSE value that states whether I want to include the specific name of this category (A5 to E5 cells). I then used an array formula in an 8th column to see whether each entry would yield a new combination. That is, it creates a column that counts - and thus detect - only the new combinations that the data implies. Here is the formula:
In column H:
Column G being the combinations and H the counter. I then use a INDEX/MATCH formula to retrieve every different combination and put it in the column I. Then I used a SUMIF to retrieve the sum of the heights and of all entry whose attributes/dimensions match this specified combination. I use a second SUM(IF()) array formula to count how many entries that combination entails. The former divided by the latter would yield the desired average. The formulas:
Respectively in Column I, J, K and L (italic texts are dynamic ranged names):
Column F being the height column. So I have now all the different averages in column L that are updated dynamically upon the selection of the desired dimensions. Some INDEX,MATCH,OFFSET,LARGE and IF functions latter - it would be too tedious and irrelevant to explain these steps - I was able to construct on another sheet (in order to have a clearer view, the first sheet being some sort of computational sheet) a list that retrieve these combinations and their average depending on the dimension I specify (with the help of check boxes) and a cell that specify a certain height that limits the averages retrieved (for example I only want the height averages above 180cm depending on the eyes color and the nationality. I would look something like this:
X => 180 Include: Eye Color: True Hair Color: False Nationality: True Hometown: False Mother's Name : False
Dimensions: Average:
BlueEyedFrench 186
BlueEyedSwiss 184
BrownEyedFrench 183
GreenEyedGerman 181
I thus created a pivot list. You may wonder why I didn't just use a pivot table. The reasons are several:
1) It was easier this way to work with this five dimensions
2) I needed to sort them to easily check the highest or the lowest values
3) I need eventually to compute more complex metrics than a simple arithmetic average
So now what I want to do - and fail how to figure it - is: I want to create a macro that goes through all the possible combinations implied by the dimensions - that is, 2^5=32 - to retrieve and store all combinations (from 1 to 5 dimensions) that are above a certain limit, for example above 190. It would retrieve something like this:
Dimensions: Average:
BlueEyedBrownHairedFrench 195
English 191
BrownHairedGermanMunich 193
etc..
Basically I want to create a macro that loop throughout all the possible dimensions to retrieve the desired values
I was thinking on creating a boolean vector for the set of possible dimensions and uses For and Each statements to loop through all the data. But I fail how to picture it, especially the part that consider to go through all the 32 dimensions. Since the values are only displayed for a combination at the time, I reckon the macro need to interact with the TRUE/FALSE statements somehow in order to retrieve all the datas.
Does someone have an idea?
Thank your to those who take the time to read all through this topic and that may have find some interest in my problem! I am looking forward to hear the thought of those who dedicated some of their time to it
Cheers,
nickice96
It is the first time I made an entry in this forum to ask for a bit a help, so far the existent threads have been very helpful to solve my problems and thank you for that. But this time, I fear that my problem may be to specific - or there is too much implications I am ignorant of - as I did not find a solution in existent threads in this website or others. So I hence tried to be to most concise with my title. I apologise in advance if it may sound a bit bizarre. So I'll try to explain my set up and what I want to do with the most explicitly.
So I have a data set of more or less 5000 entries. Each entry has five different attributes/dimensions but only one value. I cannot disclose my data due to diligence issues so I would use a generic example. Let's say I have the height (the value) of 5000 people, for each person I have his hair color, eye color, his nationality, the city he were born and the name of his mother (the 5 dimensions).
My goal was to compute the average height of people depending on the dimensions I wanted to include or not. So I could seek for example the average height of blue-eyed and blond-haired people or swiss brown-haired people whose mother is called Heidi. I thus created a 7th column in my data set that concatenate together only the desired dimensions. I would hence create different some sort of combinations and being able to recuperate their average weight. It looks like this:
The first entry is in row 5
In column G:
Rich (BB code):
=IF($AR$2=TRUE;A5&"/";"")&IF($AS$2=TRUE;B5&"/";"")&IF($AT$2=TRUE;C5&"/";"")&IF($AU$2=TRUE;D5&"/";"")&IF($AV$2=TRUE;E5;"");"")The AR2 to AV2 cells contains TRUE/FALSE value that states whether I want to include the specific name of this category (A5 to E5 cells). I then used an array formula in an 8th column to see whether each entry would yield a new combination. That is, it creates a column that counts - and thus detect - only the new combinations that the data implies. Here is the formula:
In column H:
Rich (BB code):
{=SUM(IF(COUNTIF(G$5:G5;G5)<>1;0;COUNTIF(G$5:G5;G5));H4)}Column G being the combinations and H the counter. I then use a INDEX/MATCH formula to retrieve every different combination and put it in the column I. Then I used a SUMIF to retrieve the sum of the heights and of all entry whose attributes/dimensions match this specified combination. I use a second SUM(IF()) array formula to count how many entries that combination entails. The former divided by the latter would yield the desired average. The formulas:
Respectively in Column I, J, K and L (italic texts are dynamic ranged names):
Rich (BB code):
=INDEX(ColumnG;ColumnH);MATCH(ROW(A1);INDIRECT(ColumnH);0);;1)
=SUMIF(ColumnG;$I5;ColumnF)
{=SUM(IF(ColumnG)=$I5;1;0))}
=J5/K5Column F being the height column. So I have now all the different averages in column L that are updated dynamically upon the selection of the desired dimensions. Some INDEX,MATCH,OFFSET,LARGE and IF functions latter - it would be too tedious and irrelevant to explain these steps - I was able to construct on another sheet (in order to have a clearer view, the first sheet being some sort of computational sheet) a list that retrieve these combinations and their average depending on the dimension I specify (with the help of check boxes) and a cell that specify a certain height that limits the averages retrieved (for example I only want the height averages above 180cm depending on the eyes color and the nationality. I would look something like this:
X => 180 Include: Eye Color: True Hair Color: False Nationality: True Hometown: False Mother's Name : False
Dimensions: Average:
BlueEyedFrench 186
BlueEyedSwiss 184
BrownEyedFrench 183
GreenEyedGerman 181
I thus created a pivot list. You may wonder why I didn't just use a pivot table. The reasons are several:
1) It was easier this way to work with this five dimensions
2) I needed to sort them to easily check the highest or the lowest values
3) I need eventually to compute more complex metrics than a simple arithmetic average
So now what I want to do - and fail how to figure it - is: I want to create a macro that goes through all the possible combinations implied by the dimensions - that is, 2^5=32 - to retrieve and store all combinations (from 1 to 5 dimensions) that are above a certain limit, for example above 190. It would retrieve something like this:
Dimensions: Average:
BlueEyedBrownHairedFrench 195
English 191
BrownHairedGermanMunich 193
etc..
Basically I want to create a macro that loop throughout all the possible dimensions to retrieve the desired values
I was thinking on creating a boolean vector for the set of possible dimensions and uses For and Each statements to loop through all the data. But I fail how to picture it, especially the part that consider to go through all the 32 dimensions. Since the values are only displayed for a combination at the time, I reckon the macro need to interact with the TRUE/FALSE statements somehow in order to retrieve all the datas.
Does someone have an idea?
Thank your to those who take the time to read all through this topic and that may have find some interest in my problem! I am looking forward to hear the thought of those who dedicated some of their time to it
Cheers,
nickice96
