Find Key Word, Then Find Min, Max & Median Values

Hi Group,

I have a table of values & I wish to find a key word, such as "rate" then, find the min, max or median value

How would I set this up?

Hi Beamernsw,

Wow! It works!

The function is not what I was expecting but it seems to work well.

What does the contents within the quotes do?

Can this function work find wildcards or key word, such as:

XXX Rate
ABC Rate
123 Rate

-- Where I just look for the word "rate"?

I'll see if I can apply this to my worksheet.


Thanks again...

Hi Beamernsw & Others,

I attached an example file for reference. I'm actually looking to do a couple of things.

As you will see in the first file, I'm looking for MIN, MAX, MEDIAN horizontally between different positions & companies. Highlighted areas in yellow is where I need assistance.

In the second table, I'm looking for min & max dates vertically based from search criteria

See attached

Thanks again for the assistance.

For min values enter in L5 and fill down

Formula:

Please Login or Register  to view this content.

For Max enter in M5 and fill down

Formula:

Please Login or Register  to view this content.

For Median enter in N5 and fill down

Formula:

Please Login or Register  to view this content.

What you are trying to calculate in the second part of your problem is a mystery to me as I don't see any dates or tasks associated with your data.

Hi newdoverman,

Great solutions for the first table. However, if this table was bigger or wider, could another function type work where it would like for the key word, then across or down columns - depending on intent needs?

Now that I'm seeing this - For Min values - I want to exclude $0.00 rates or find the next smallest value.


As for my second table,

I'm simply looking for the following types of tasks & some type of function that will give me the First Start or minimum & Last Start or maximum dates (in the yellow shaded areas)

First Start Date Last Start Date
Task 1 04/12/16
Task 2
Task 3
Task 4


I just noticed that I didn't drag the tasks all the down but it should be.

Thanks

For a larger table of values, enter an X above each of the columns that have values to be included in the calculations (every second column)

All these are ARRAY entered (Ctrl + Shift + Enter)
Formula for Min if X values are in row 3 from D3 to J3
Enter in L5 and fill down

Formula:

Please Login or Register  to view this content.

Max value enter in M5 and fill down

Formula:

Please Login or Register  to view this content.

Median
Enter in N5 and fill down

Formula:

Please Login or Register  to view this content.

I still don't understand the second part of your problem. Where do the Tasks come from? They are not in your data.

I must be slow today.
Second part of the problem. I only used the dates that had the Tasks beside them
First Start ARRAY enter in D16 and fill down. Format as Date

Formula:

Please Login or Register  to view this content.

Last Start Array enter in F16 and fill down. Format as Date

Formula:

Please Login or Register  to view this content.

Hi newdoverman,

Something is not working with above examples.

I revised my example file a little bit.

With Table 2

I'm looking for "Task 1" within the Log Data table below it & wanting to find the First or minimum date for "First Start Date". "Last Start Date" is looking for the last date or maximum date when "Task 1" is found

Once have functions, then I want to drag that down in Table 2


See updated file for both

Copy of your workbook with the formulae properly applied.

Hi newdoverman

Fantastic Solutions!

Thank you for the assistance.

Hi newdoverman,

It doesn't appears that wildcard word searches work. Why is this?

{=MIN(IF(($D$4:$J$4="X")*(D5:J5>0),D5:J5))}

The "X" makes it work but would've been nice to use the column headers & look up the word "Rate".

Doesn't -Matter - You gave the needed results.

Thanks

Thank you for the feedback. Not all functions can use wildcards. It would be nice if they did but for now that is the way it is.

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

Hi,

I have the following which works great!

{=MIN(IF(($G$33:$G$1000=G7)*($I$33:$I$1000),$I$33:$I$1000))}

However, in some of my column cells, I have "N/A" in it. Now, instead of the minimum date, I'm getting a "###" result.

How to ignore the "N/A"?

Thanks

To add above questions,

In addition to having, "N/A" in some cells, I may also have "TBD" down the same column. How to ignore both of these to make the following work?

{=MIN(IF(($G$33:$G$1000=G7)*($I$33:$I$1000),$I$33:$I$1000))}

Hi Group,

Not sure what I did, but I managed to get the function working just fine...

Thanks