Large function with Criteria

[ruthschoenb]

I am working on a formula/function to find the top 5 highest $ amounts and add them to my summary page under the column O.P.. It's not working as expected and not returning a value. When I run the function ctrl+shift+enter I am getting a number error. The column I am referencing is a number, I set it up as currency received the error and changed it from currency to number.

{=LARGE(IF(Data!E2:E83="*tock*",Data!I2:I83),1)}

I am also trying to figure out based on the top 5 O.P to populate the "Namespace" field with the correlating top 5 $ amount. I haven't added that part yet because I am trying to figure it out.

Thank you, please see attached sample.

[63falcondude]

Try this:

=LARGE(IF(ISNUMBER(SEARCH("tock",Data!E2:E83)),Data!I2:I83),1) Ctrl Shift Enter

[Glenn Kennedy]

Are you really still using Excel 2003 or earlier (outdated .xls format)? Please update profile with current Excel version. "Excel for Mac" tells me nothing about how old/which version you have.

[ruthschoenb]

WOW thank you that worked! Do you have any idea about how I would get the correlating data for the namespace field? I would like to have the information on my summary page.

Like this:

Namespace . O.P
one-Tock-only1-ci-a 29.27

[63falcondude]

You're welcome.

Manually fill in the desired results then re-upload your sample and we will create a formula for you.

[ruthschoenb]

New sample of the spread sheet - with Namespace filled in manually

[63falcondude]

The formulas in C11:C14 were not entered as array formulas and are therefore not producing the correct results.

To enter the formula as an array, you must use Ctrl Shift Enter instead of just Enter.
You can look up what keys to use on a Mac.

That being said, I believe I know what you are trying to do here.

Try these:

C10 =LARGE(IF(ISNUMBER(SEARCH("tock",Data!E$2:E$83)),Data!I$2:I$83),A10) Ctrl Shift Enter
B10 =INDEX(Data!E:E,SMALL(IF(ISNUMBER(SEARCH("Tock",Data!E$2:E$83))*(Data!I$2:I$83=C10),ROW(Data!E$2:E$83)),1)) Ctrl Shift Enter
Drag both formulas down.

See attachment.

[Glenn Kennedy]

ruthschoenb. Please address the questions that I asked at Post 3.

[ruthschoenb]

Thank you soooo much! This works perfectly!

One last question if the data in my "data" sheet changes do you know how I can have my summary update at the same time? The number of row's change for data and if I have to go in and change my summary each time its a lot of manual changes.

Thanks

[ruthschoenb]

So the spread sheet was a sample with data I changed names of items. I updated on my real spread sheet modifying the "tock" but now I am once again getting the Lookup error. I can't upload the real sheet. :/

The fields are correct "*criteria*" and the 4 amount is correct as well in the data sheet.

[63falcondude]

I would love to continue helping you but out of respect for Glenn, I am going to hold off until you address his question.

[AliGW]

@ ruthschoenb - Are you deliberately ignoring Glenn's posts to this thread?

[ruthschoenb]

I am new to a MAC and trying to find out what version I am running. Windows I would just go to the "about" but MAC does not have the option inside of excel. :/ .

[ruthschoenb]

Glenn - Sorry for the late update I finally found it.

Version 16.16.12 (1907115)

[63falcondude]

The number of row's change for data and if I have to go in and change my summary each time its a lot of manual changes.

You can change your data into a table then refer to the entire data column or just make the ranges large enough to account for new data.

So the spread sheet was a sample with data I changed names of items. I updated on my real spread sheet modifying the "tock" but now I am once again getting the Lookup error. I can't upload the real sheet. :/

The only thing that I can suggest without seeing a sample would be to make sure that you are entering your formulas as an array.

If you are able to upload a small representative sample where the formulas do not work, we can take a further look at what's going on.

[AliGW]

Glenn - Sorry for the late update I finally found it.

Version 16.16.12 (1907115)

Please update your user profile, as requested earlier in the thread, with this information. Members rely on knowing which version you are using when suggesting solutions.

[63falcondude]

Simply "Excel 2016 for MAC" should do.

[Glenn Kennedy]

In B10, copied down:
=INDEX(Data!E:E,AGGREGATE(15,6,ROW(Data!$E$2:$E$83)/((ISNUMBER(SEARCH("tock",Data!$E$2:$E$83)))*(Data!$I$2:$I$83=C10)),COUNTIF($C$10:C10,C10)))

in C10, copied down:
=AGGREGATE(14,6,Data!$I$2:$I$83/(ISNUMBER(SEARCH("tock",Data!$E$2:$E$83))),A10)

These are ordinary formulae. No need to CTRL-SHIFT-ENTER (or Mac equivalent). Just enter. THIS is why I wanted to know your Excel version.

I have updated the file format to Excel 2007+. Is there any reason why you need compatability with (almost) prehistoric versions of Excel?

[ruthschoenb]

here is more data thanks you

my actual data is 1254 rows.

[Glenn Kennedy]

Formulae in your new sheet. Ranges set to cover 2000 rows.

You must learn not to ignore questions, especially those from Forum Moderators. We are all here to help, but you need to help us to help you. Please see the unanswered questions in my last Post ... and answer them.

[63falcondude]

You have done two things:

1) Modified the formula to include wildcards. They are not needed.
2) Did not enter the column B formula as an array.

I have fixed both issues and re-uploaded the sample from post #19.

Note that you can drag the formulas down once you enter then (correctly) into B10 and C10.

[ruthschoenb]

Thank you very much, I appreciate the help!!! if I swap out the data will I have to re-run each function or will it pickup the new data?

[Glenn Kennedy]

Who are you talking to?

[ruthschoenb]

Who are you talking to?

I was speaking to 63falcondude he helped me with my formula with a small amount of my data.
thank you

[Glenn Kennedy]

Clearly you are ignoring everything I havealso tried to do to help you.. I hope you get sorted out, but I'm out. Goodbye.

[63falcondude]

Thank you very much, I appreciate the help!!! if I swap out the data will I have to re-run each function or will it pickup the new data?

Yes but you would be better off just putting the formulas onto your workbook making sure to enter them as an array.

If you change the formulas at all, you will have to enter them as an array formula again.