Conditional Divide Formula

[Snoopyy]

Hello friends, I was hoping I'd be able to get some help for a certain project of mine concerning navigating data in Excel sheets.

Suppose I have two columns, A and B, and in these columns there are different values. In column A, some names are the same and some are different. For example, column A may have values like "dog, house, street, door" and column B has different values like "Rock, Bed".

Column A Column B
Dog Rock
Dog Rock
Dog Rock
House Rock
Street Bed
Dog Rock
Door Bed

Now, I want to divide Column A/Column B with values in Column A that share the same values in Column B. Column A has the values "Dog" and "House" which both have "Rock" in Column B across from it, so I would want to divide them to get Dog/Rock, Dog/Rock, Dog/Rock, House/Rock, Dog/Rock. Also, "Street" and "Door" each have "Bed" in Column B, so I would want Excel to divide them to get Street/Bed and Door/Bed.

I know this may seem complicated, but it is precisely why I took this to this forum! I hope one of your Excel masters have a trick in mind that you'll be able to share with me. Thanks!

 

[Snoopyy]

Also, just to clarify, I'm looking for a formula which would accomplish this. Thanks!

 

[FDibbins]

Not really sure I understand what you want, unless you just want to combine the 2 sets of text (you probably know you cannot divide text?)

So maybe this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Dog[/td][td]Rock[/td][td]Dog/Rock[/td][/tr]

[tr][td]

2​

[/td][td]Dog[/td][td]Rock[/td][td]Dog/Rock[/td][/tr]

[tr][td]

3​

[/td][td]Dog[/td][td]Rock[/td][td]Dog/Rock[/td][/tr]

[tr][td]

4​

[/td][td]House[/td][td]Rock[/td][td]House/Rock[/td][/tr]

[tr][td]

5​

[/td][td]Street[/td][td]Bed[/td][td]Street/Bed[/td][/tr]

[tr][td]

6​

[/td][td]Dog[/td][td]Rock[/td][td]Dog/Rock[/td][/tr]

[tr][td]

7​

[/td][td]Door[/td][td]Bed[/td][td]Door/Bed[/td][/tr]
[/table]
C1=A1&"/"&B1
copied down

 

[Snoopyy]

I apologize, let me rephrase!
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E (Unique Values in A)[/TD]
[TD]F(Average for Column B for Each Unique Value in A)[/TD]
[TD]G (Unique Values in C)[/TD]
[TD]H (Average for Column D for Each Unique Value in C)[/TD]
[TD]I (Average Unique in Column A / Average Unique in Column B)[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]2[/TD]
[TD]Door[/TD]
[TD]3[/TD]
[TD]Dog[/TD]
[TD]3/2[/TD]
[TD]Door[/TD]
[TD]7/3[/TD]
[TD]((3/2)/(7/3))[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]1[/TD]
[TD]Door[/TD]
[TD]2[/TD]
[TD]Shoe[/TD]
[TD]3[/TD]
[TD]Bed[/TD]
[TD]7/4[/TD]
[TD]((3)/(7/3))[/TD]
[/TR]
[TR]
[TD]Shoe[/TD]
[TD]3[/TD]
[TD]Door[/TD]
[TD]2[/TD]
[TD]House[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]((3)/(7/4))[/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD]2[/TD]
[TD]Bed[/TD]
[TD]1[/TD]
[TD]Rock[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]((1)/(7/4))[/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD]3[/TD]
[TD]Bed[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD]4[/TD]
[TD]Bed[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rock[/TD]
[TD]1[/TD]
[TD]Bed[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In Column I, you only want to divide the averages in Column F/ Column H if that unique value in Column A shares a value in Column C, so since dog and shoe in Column A each share "Door" in Column C, I would divide the unique average for dog over the average for door, and also divide the unique average for shoe over the average for door.

I realize this is a challenge (for me at least), but I bet you guys probably do this stuff in your sleep!