Most common price associated with a product

[joshbjames]

This is similar to a question I have asked before. Basically, I have a list of products and the product number repeats on a new row for every price variance we have sold it for. I would like to find the price we have sold it for most frequently.

Variables to account for would be:
1 - It has only been sold at one price. (Return that value).
2 - Two different prices occur the exact same amount of times. (Return the lowest of the 2 prices).
3 - No particular price occurred more than another (Return the lowest price).

Here is some sample data with as many scenarios I could think of and the result I'd like.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product Number[/TD]
[TD]Price[/TD]
[TD]Most Common[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7890[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7890[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Care to state per record which of the rules (Rule-1, Rule-2, and Rule-3) applies (in order to determine "most common" price?

 

[joshbjames]

1234 - One price occurs most often, return that price.
5678 - Only one price exists, return that price.
9012 - The two most frequent prices occur the same amount of times, return the lower of the two.
3456 & 7890 - All the prices are different, return the lowest.

 

[Aladin Akyurek]

1234 - One price occurs most often, return that price.
5678 - Only one price exists, return that price.
9012 - The two most frequent prices occur the same amount of times, return the lower of the two.
3456 & 7890 - All the prices are different, return the lowest.

Thanks. The following seems to cover above rules. It needs more testing though...

In C2 control+shift+enter, not just enter, and copy down:

=IF(COUNT(MODE.MULT(IF($A$2:$A$15=$A2,$B$2:$B$15)))>1,MIN(MODE.MULT(IF($A$2:$A$15=$A2,$B$2:$B$15))),IFERROR(MODE(IF($A$2:$A$15=$A2,$B$2:$B$15)),MIN(IF($A$2:$A$15=$A2,$B$2:$B$15))))

 

[joshbjames]

Thanks, I will try this first thing in the morning and let you know! I tend to explain things in the most complicated way.

The basic idea is - Find the most frequent price. If nothing is more frequent return the lowest option. If two or more values tie for most frequent return the lowest (hopefully, that clarifies things).

 

[markmzz]

The basic idea is - Find the most frequent price. If nothing is more frequent return the lowest option. If two or more values tie for most frequent return the lowest (hopefully, that clarifies things).

Hi!

Try the Array Formula below too:

In C2 and copy down - use Ctrl+Shift+Enter to enter the formula

=IFERROR(MIN(MODE.MULT(IF($A$2:$A$15=A2,$B$2:$B$15))),MIN(IF($A$2:$A$15=A2,$B$2:$B$15)))


[TABLE="class: grid, width: 317"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Product Number[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Most Common[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1234[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5678[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]9012[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]9012[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]9012[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]9012[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]9012[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]3456[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]3456[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]3456[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]7890[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]7890[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]******[/TD]
[TD="align: center"]**************[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[joshbjames]

Thank you both so much! These were both helpful. You all helped me solve a problem that has been plaguing us at work for a very long time.