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...

	A​	B​	C​
1​	Dog	Rock	Dog/Rock
2​	Dog	Rock	Dog/Rock
3​	Dog	Rock	Dog/Rock
4​	House	Rock	House/Rock
5​	Street	Bed	Street/Bed
6​	Dog	Rock	Dog/Rock
7​	Door	Bed	Door/Bed

C1=A1&"/"&B1
copied down

 

[Snoopyy]

I apologize, let me rephrase!

A	B	C	D	E (Unique Values in A)	F(Average for Column B for Each Unique Value in A)	G (Unique Values in C)	H (Average for Column D for Each Unique Value in C)	I (Average Unique in Column A / Average Unique in Column B)
Dog	2	Door	3	Dog	3/2	Door	7/3	((3/2)/(7/3))
Dog	1	Door	2	Shoe	3	Bed	7/4	((3)/(7/3))
Shoe	3	Door	2	House	3			((3)/(7/4))
House	2	Bed	1	Rock	1			((1)/(7/4))
House	3	Bed	3
House	4	Bed	2
Rock	1	Bed	1

<tbody>
</tbody>

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!