Find The Lowest Corresponding Value For A Repeated Value

[rovaniemi]

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.

[rollis13]

Cross-post: Excelguru.ca (http://www.excelguru.ca/forums/newreply.php?do=postreply&t=1848)

[Rick Rothstein]

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.

[rovaniemi]

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

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

[Rick Rothstein]

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.

[rovaniemi]

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.

[Admin]

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

[rovaniemi]

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.

[Excel Fox]

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

[Rick Rothstein]

rovaniemi, please read http://www.excelfox.com/forum/f25/message-to-cross-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 (http://www.excelguru.ca/forums/newreply.php?do=postreply&t=1848) 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.

[rovaniemi]

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 (http://www.excelguru.ca/forums/showthread.php?1848-Finding-the-lowest-value-from-repeated-data)

[rovaniemi]

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.

[Excel Fox]

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.

[rovaniemi]

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