Ranking prices at same facilities and identifying minimums

[nirvehex]

Hi All,

I'm a bit stumped on how to setup a calculation in Excel.

In column A I have a list of Facility ID's and in column K I have prices. What I am trying to do is create a formula in column O that outputs "Minimum" if in column K it is the lowest price for the facility ID in column A. Example Table below. If there is a tie in price, I'd like to only identify one of them as "minimum."

Facility ID	Price	Output
FAC000001	51.96	Minimum (reason is only one price for this facility)
FAC000002	1.0392	Minimum (reason is lowest price for this facility)
FAC000002	4.33
FAC000004	.5196	Minimum (reason is lowest price for this facility - only put minimum on one line if there is a tie)
FAC000004	.5196
FAC000004	2.165
FAC000005	4.33
FAC000010	.5196	Minimum (reason is lowest price for this facility)

Is there some sort of ranking/array function I can use?

Thanks!

-M

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[nirvehex]

Thanks Fluff, Happy New Year to ya! I just updated my Excel version by doing what you suggested. I'm on Office 365 ProPlus.

 

[Fluff]

Thanks for that, how about

=IF(AND(COUNTIFS(A$2:A2,A2,K$2:K2,K2)=1,MINIFS(K:K,A:A,A2)=K2),"Minimum","")

 

[nirvehex]

Brilliant! That worked. Thanks!!

 

[Fluff]

You're welcome & thanks for the feedback.