Hi Everyone,
I am having trouble with an excel project in which I have two columns that I feel could be merged into one. I have designed a database that keeps track of adjectives used to describe dog toys by men and women. I first track the gender of the dog, then the gender of the toy is the same as the dog. In the database, each dog has a unique database record. Each one is identified by a unique ID number. A dog might have five toys in a home, so there will be five records (one for each toy), each record repeating the owner's unique user ID.
Because for the gender-based analysis I need to know how many male vs. how many female dogs are in the database, the first record generated for any dog gets a gender tag: F or M. Because I might later analyze this in a different way, each record also gets a gender tag, F or M. So two records toys owned by the same dog might look like this:
[TABLE="width: 743"]
<tbody>[TR]
[TD="width: 238"]Owner[/TD]
[TD="width: 69"]Owner ID[/TD]
[TD="width: 61"]Gender of dog[/TD]
[TD="width: 71"]Gender of Item/Owner[/TD]
[TD="width: 162"]Adjectives[/TD]
[TD="width: 142"]Type of Book[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 743"]
<colgroup><col width="238" style="width: 179pt;"><col width="69" style="width: 52pt;"><col width="61" style="width: 46pt;"><col width="71" style="width: 53pt;"><col width="162" style="width: 122pt;"><col width="142" style="width: 107pt;"></colgroup><tbody>[TR]
[TD="width: 238"]Snickers (owned by NAME)[/TD]
[TD="width: 69, align: right"]1000272[/TD]
[TD="width: 61"]F[/TD]
[TD="width: 71"]F[/TD]
[TD="width: 162"]little[/TD]
[TD="width: 142"]chew toy[/TD]
[/TR]
[TR]
[TD]Snickers (owned by NAME) [/TD]
[TD="align: right"]1000272[/TD]
[TD][/TD]
[TD]F[/TD]
[TD="width: 162"]favorite [/TD]
[TD]rope[/TD]
[/TR]
</tbody>[/TABLE]
The "gender of dog" (the first F, given only to one record per unique owner) lets me count the number of different female or male dogs in the database, or total dogs in the database. The (second F, given to all records) lets me count how many toys were owned by female and male dogs.
This approach allows me to answer different questions about the frequency of gender of dogs, vs. frequency of items owned by dogs of particular genders.
For example, I need to know how many female dogs and their owners in the database used adjectives that describe the toys by size. Right now, I do that via a calculation that looks like:
If I want to know how many owners of male dogs using toys are describing them by size, I do this:
I feel like my current approach involves unnecessary duplication. It seems to me that there should be a more streamlined approach that requires entering gender only once, for all records. I would only count the gender flag once per unique owner ID number if I want a total count of female dogs, and I would count all instances if I want a total number of toys owned by female dogs. I know that the unique owner IDs are part of that, and I should be using them somehow in my calculations. But I don't know enough to get there.
I am having trouble with an excel project in which I have two columns that I feel could be merged into one. I have designed a database that keeps track of adjectives used to describe dog toys by men and women. I first track the gender of the dog, then the gender of the toy is the same as the dog. In the database, each dog has a unique database record. Each one is identified by a unique ID number. A dog might have five toys in a home, so there will be five records (one for each toy), each record repeating the owner's unique user ID.
Because for the gender-based analysis I need to know how many male vs. how many female dogs are in the database, the first record generated for any dog gets a gender tag: F or M. Because I might later analyze this in a different way, each record also gets a gender tag, F or M. So two records toys owned by the same dog might look like this:
[TABLE="width: 743"]
<tbody>[TR]
[TD="width: 238"]Owner[/TD]
[TD="width: 69"]Owner ID[/TD]
[TD="width: 61"]Gender of dog[/TD]
[TD="width: 71"]Gender of Item/Owner[/TD]
[TD="width: 162"]Adjectives[/TD]
[TD="width: 142"]Type of Book[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 743"]
<colgroup><col width="238" style="width: 179pt;"><col width="69" style="width: 52pt;"><col width="61" style="width: 46pt;"><col width="71" style="width: 53pt;"><col width="162" style="width: 122pt;"><col width="142" style="width: 107pt;"></colgroup><tbody>[TR]
[TD="width: 238"]Snickers (owned by NAME)[/TD]
[TD="width: 69, align: right"]1000272[/TD]
[TD="width: 61"]F[/TD]
[TD="width: 71"]F[/TD]
[TD="width: 162"]little[/TD]
[TD="width: 142"]chew toy[/TD]
[/TR]
[TR]
[TD]Snickers (owned by NAME) [/TD]
[TD="align: right"]1000272[/TD]
[TD][/TD]
[TD]F[/TD]
[TD="width: 162"]favorite [/TD]
[TD]rope[/TD]
[/TR]
</tbody>[/TABLE]
The "gender of dog" (the first F, given only to one record per unique owner) lets me count the number of different female or male dogs in the database, or total dogs in the database. The (second F, given to all records) lets me count how many toys were owned by female and male dogs.
This approach allows me to answer different questions about the frequency of gender of dogs, vs. frequency of items owned by dogs of particular genders.
For example, I need to know how many female dogs and their owners in the database used adjectives that describe the toys by size. Right now, I do that via a calculation that looks like:
=SUMPRODUCT((Descriptions!C2:<wbr>C934="F")*(Descriptions!T2:<wbr>AD934="size")) <-- this calculation relies on the first gender flag.
If I want to know how many owners of male dogs using toys are describing them by size, I do this:
=SUMPRODUCT((Descriptions!D2:<wbr>D934="F")*(Descriptions!T2:<wbr>AD934="size")) <-- this calculation relies on the second gender flag.
I feel like my current approach involves unnecessary duplication. It seems to me that there should be a more streamlined approach that requires entering gender only once, for all records. I would only count the gender flag once per unique owner ID number if I want a total count of female dogs, and I would count all instances if I want a total number of toys owned by female dogs. I know that the unique owner IDs are part of that, and I should be using them somehow in my calculations. But I don't know enough to get there.
