Find The Lowest Corresponding Value For A Repeated Value

Hi everyone

I am new to this forum and very bad in excel. I have hit a roadblock right now. I have attached an excel file.

My issue is this:

I want to find the lowest value associated with a each codes (212, 223, 230, 242) corresponding to its value in the C column.

I have tried using this formula - =IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF(($B$2:$B$17=B2 )*($C$2:$C$17<>0),$C$2:$C$17)))

When using this formula, the value associated with Column B is changing as the cell changes, but the value of Column C doesn't change accordingly, when the equation is applied in the Column D. The result remains the same for the entire D column.

The resultant value of 212 is 3. The values for 223, 230, 242 should be 1, 3, 5 respectively. I would be very much obliged if someone can derive one formula with which I can get the required results.

The value of the C column should change (finding the lowest value)

Thank you all in advance for your replies and efforts.

Cross-post: Excelguru.ca

Give this array-entered** formula a try...

=IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF(($B$2:$B$17=B2 ),$A$2:$A$17)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

IMPORTANT NOTE: Turn Automatic Calculations back on... for some reason you have it set to Manual right now.

I don't understand. Did I make any mistake?

Thank you Rick. But when I entered the formula the result it gave me was 41085 rather than 1,3,5 respectively. Yes I turned on automatic calculations.

Quote:

---

Originally Posted by rovaniemi

Thank you Rick. But when I entered the formula the result it gave me was 41085 rather than 1,3,5 respectively. Yes I turned on automatic calculations.

---

Sorry, 41085 is the serial number for a date... I mistakenly thought you wanted the earliest date, not the minimum value in Column C. Here is the array-entered** formula modified to return values from Column C...

=IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF(($B$2:$B$17=B2 ),$C$2:$C$17)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Rick thanks again. Using the above formula gives 0 as result.I don't know If I have been explaining things correctly. When I said minimum value what I meant is not zero.

We don't need zero here. The minimum value for 212 is 3. Likewise the minimum value for 223, 230, 242 should be 1, 3, 5 respectively. That is the formula I am looking for.

Thank you so much for the reply and patience.

I have attached image and excel file of how the answer should be.

Hi

Tweak Rick's formula like this one.

=IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF($B$2:$B$17=B2, IF($C$2:$C$17<>0,$C$2:$C$17))))

Entered as an array formula

A million thanks to Admin and Rick. You guys are simply brilliant. The formula worked like charm. Thank you so so so much for your help. May God bless you all.

rovaniemi, please read http://www.excelfox.com/forum/f25/me...-posters-1172/ for your response to rollis13's post above

Quote:

---

Originally Posted by Excel Fox

rovaniemi, please read http://www.excelfox.com/forum/f25/me...-posters-1172/ for your response to rollis13's post above

---

And then after you read the link Excel Fox provided for you in Message #10, please go back to Excelguru.ca and post, first, that you received an answer to your question elsewhere and then, second, post a link to this thread so volunteers over there will be able to see the responses here that you considered as having answered your question.

It was my mistake. I have informed the excelguru.ca forums that I found my solution here. I am unable to post the link to this thread there as I don't have enough posts in that forum. Thank you all again.

Here's the link - Finding the lowest value from repeated data

Hi everyone,

I have a new doubt. It is regarding the same worksheet. I need to find the average of the C column.

Assuming X(212, 223, 242) have 4 values each. I would like to find the average of each X column values.

Thank you all for your patience and support. I am eagerly awaiting for your replies.

rovaniemi,

If your question is not directly linked to what's already being discussed in the thread, please start a new thread. Your query above doesn't have a direct relation to the original thread. Hope you'll follow this guideline in the future.

I am really sorry. I will follow the guidelines in future. Thank you. I will open a new thread.