Find min value in column across from another column ending in specific text.

[rcane]

good morning,

I have two columns. The first column has varying words, but in all cases they either end with "FO" or "CA".

The second column is a list of percentages.


I'm just trying to find the lowest percentage when the text ends in CA as well as FO.

I made it work by creating a helper column that contained only FO's or CA's, but I'd like to use =RIGHT(column, 2) as there are about 100 rows to examine, and I avoid helper column when I can.

I can test cells successfully when using =right, but I can't seem to get it to work with an array.

this is a sample of the data range.


The two answers should be 7% and 89.5%--specifically the text immediately to the left of each.

Any thoughts?

Screen Shot 2018-02-16 at 9.18.08 AM.png

[AliGW]

Try this:

=MIN(IF(RIGHT(word_column,2)="CA",%_column))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[rcane]

It wasn't a duplicate thread. It was a stuck browser and started anew. had I noticed it I would have cancelled it. You needn't type with a condescending tone and exclamatory punctuation.

Regardless your cell only produces a result with regard to FO and not CA.

{=INDEX(C7:C30,MATCH(MIN(IF(RIGHT(C7:C30,2)="fo",D7:D30)),D7:D30,0))}

where C:C = textual cells, and D:D = %'s.

I've also tested that specific cell (where I know the answer sits) using =right(c25, 2). It produces "ca". So, I know it isn't a textual issue such as empty spaces I've missed.

[AliGW]

So are you wanting to find the minimum where the codes end in CA or FO rather than CA and FO separately?

[rcane]

there will be two cells--one for each min value. copying the cell and charging the target text from FO to CA should find it, as the data ranges are the same.

[AliGW]

OK, so use this in one cell:

=MIN(IF(RIGHT(word_column,2)="CA",%_column))

and this in the other:

=MIN(IF(RIGHT(word_column,2)="FO",%_column))

If that's not what you want, then you are going to need to give more detail. And attach a workbook.

[davsth]

I think Ali was nearly there

=MIN(IF(right(C7:C11,2)="fo",IF(D7:D11<>"",D7:D11))) entered as an array shft ctril enter as the blanks can get returned a 0

[AliGW]

Ah, yes - the blank cells. Well spotted!

[rcane]

I think Ali was nearly there

=MIN(IF(right(C7:C11,2)="fo",IF(D7:D11<>"",D7:D11))) entered as an array shft ctril enter as the blanks can get returned a 0

Agreed, but it produced an answer with FO--not with CA. It should have failed twice if blanks were culprit. Same D:D column. I'm checking the textual column for hidden spaces or formatting.

[AliGW]

Post the workbook, as I suggested before.

[rcane]

I think Ali was nearly there

=MIN(IF(right(C7:C11,2)="fo",IF(D7:D11<>"",D7:D11))) entered as an array shft ctril enter as the blanks can get returned a 0

Thanks davsth. It didn't like the blanks. I'm testing it back out with the full sheet now to see if there was anything else overlooked before calling it solved.

[AliGW]

I mentioned the blanks in post #8.

[rcane]

no, you congratulated DAV for finding them.

[AliGW]

No, I congratulated him for amending the formula I had offered to account for them. I mentioned it so that YOU would know why the amendment was needed.

[rcane]

No, I congratulated him for amending the formula I had offered to account for them. I mentioned it so that YOU would know why the amendment was needed.

somebody wants a trophy.

Your posts mentioned nothing about blanks. Your only response that I can see between 1-8 posts is

"Ah, yes - the blank cells. Well spotted! "

Your mention had nothing to do with making sure "I" would see it. I've been following the thread just fine. And, you didn't indicate "why" it was needed, as [he] did.

[AliGW]

Oh, dear ... Well, I tried. Hope you get it sorted.

[rcane]

Oddly, it's not working.

The original workbook has 12000 lines that produce the table in question above.

I copied that table to a new book as waiting for it to chew on that many lines took a while to even troubleshoot the smallest of things..

As a stand alone table the solution here works. When referencing those cells as they are formulaically produced, it does not. I'm combing for blank cell issues again.

the "blank cells" in the % column test blank using if="", but fail when testing with "ISBLANK()"

[AliGW]

Why don't you attach the workbook? Two or three sets of eyes are better than one.

[rcane]

it's literally got a ton of sensitive data in it. the little bit i showed was permissible. i'm trying to clean it up for transport right now.

it would appear the "blanks" we've been dealing with aren't blank enough. they return "" based on other things happening. that's making them fail ISBLANK().

[AliGW]

So if they contain formulae, they are not blank. That means that =ISBLANK() will return FALSE, but ="" (which looks at the value returned by the formula) returns TRUE. You are going to need to introduce a third criterion to the formula. You should be able to do this with a criterion like this:

=MIN(IF(RIGHT(C7:C11,2)="FO",IF(D7:D11<>"",IF(D7:D11<>0,D7:D11))))

[rcane]

unless i'm using VBA I think i can just duplicate the results table. The first production will use na()'s instead of returning "". This table will be invisible to the user. The second table will be for show--rendering out those na()'s for "".

Seems to make cells test positively for ISBLANK() this way.

[AliGW]

Did you try the formula amendment? If so, did it work? I'm just curious.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[rcane]

Nope, not solved.

I cannot attach the book. Too much data to redact.

I took the %'s column and ran a simple nested IF test to test for: isnumber(), ="", isblank(), iserror(), isna(). In all 72 rows, the blank cells returned "empty" which was my criteria if(cell=""). I did this for the original sheet as well as those figures carried over to the table I've show here.

All the cells that had a number (for example 52.5%) returned false.

The original data that contained that % column was converted from a PDF file.
It seems like all the formula solutions here aren't much different than what I started with; the appears to be a format problem.


That worksheet is about 12,000 lines long--the specific lines needed are simply brought over to the table I've shown by way of an array that searches for the proper % figure.

[Phuocam]

Try:

=INDEX(C7:C30,MATCH(MIN(IF((RIGHT(C7:C30,2)="fo")*(D7:D30<>""),D7:D30)),IF((RIGHT(C7:C30,2)="fo")*(D7:D30<>""),D7:D30),0))

Ctrl+Shift+Enter.

[rcane]

Try:

=INDEX(C7:C30,MATCH(MIN(IF((RIGHT(C7:C30,2)="fo")*(D7:D30<>""),D7:D30)),IF((RIGHT(C7:C30,2)="fo")*(D7:D30<>""),D7:D30),0))

Ctrl+Shift+Enter.

PHUOCAM, thanks for chiming in.

I have actually managed to get several flavors of this working now. The problem wasn't actually formatting, but rather a memory issue dealing with the size of the file.

If I force clicked a different cell after applying the array I would get a "0" for the result. However, if I let the cell lie for about 15 seconds (which happened by mistake while working) I see the calculation does in fact take place.

Thank you for the contributions in the last 24 posts.