Text in Column A, numbers in Column B, and text criteria in C1

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have a similar spreadsheet where I am trying to use the array formula below; however in using this array (tailored to the layout of my worksheet) and entering with CSE I am receiving a "#num!" error. Any suggestions?

Thanks in adavance for your help!



RagDyeR wrote:

With text in Column A and numbers in Column B, and text criteria in C1,Try
31-Jul-08

With text in Column A and numbers in Column B, and text criteria in C1,
Try this *array* formula:

=LARGE(IF(A1:A15=C1,B1:B15),1)
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

This is set for the *1st* largest!

Just change the last 1 in the formula for other positions.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"TT" <TT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DBC3CEE8-C69A-477E-8AAB-1E2DF14CB8A2@xxxxxxxxxxxxxxxx
I have 2 columns of date. One with text and another with related numeric
figures. I am trying to craft a formula which will extract the largest nth
figures for a given text criteria. Any help would be appreciated. Thanks

Previous Posts In This Thread:

On Thursday, July 31, 2008 11:10 AM
T wrote:

Using LARGE function with criteria
I have 2 columns of date. One with text and another with related numeric
figures. I am trying to craft a formula which will extract the largest nth
figures for a given text criteria. Any help would be appreciated. Thanks

On Thursday, July 31, 2008 11:57 AM
yshridha wrote:

RE: Using LARGE function with criteria
d1=LARGE(IF((A1:A10=c1),B1:B10),n)
text - column A
numrics - column b
c1 = text criteria
n - nt figure
it is an array formula. enter with ctrl+shift+enter
best wishes
sreedhar

"TT" wrote:

On Thursday, July 31, 2008 12:04 PM
RagDyeR wrote:

With text in Column A and numbers in Column B, and text criteria in C1,Try
With text in Column A and numbers in Column B, and text criteria in C1,
Try this *array* formula:

=LARGE(IF(A1:A15=C1,B1:B15),1)
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

This is set for the *1st* largest!

Just change the last 1 in the formula for other positions.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"TT" <TT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DBC3CEE8-C69A-477E-8AAB-1E2DF14CB8A2@xxxxxxxxxxxxxxxx
I have 2 columns of date. One with text and another with related numeric
figures. I am trying to craft a formula which will extract the largest nth
figures for a given text criteria. Any help would be appreciated. Thanks

On Thursday, July 31, 2008 12:39 PM
T wrote:

RE: Using LARGE function with criteria
Perfect. Much appreciated

"yshridhar" wrote:

EggHeadCafe - Software Developer Portal of Choice
C# : Implement Data Access Layer independent of Physical Database Schema
http://www.eggheadcafe.com/tutorials/aspnet/43e965af-c35d-4f42-87fa-105527c433fc/c--implement-data-acces.aspx
.

Relevant Pages

  • Re: Drop down lists and vlookup? or Match?
    ... exactly the same way except that the criteria in this case is different. ... This will return an array of TRUE's or FALSE's. ... Sheet2 has the employee list. ... Does each crew have the same number of people assigned? ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Data Validation with repeaters excluded from the source list
    ... I have eliminated the helper column. ... As said before I have the source data in Column B starting from row no 3 and ... did not know that criteria could be an array. ... array why is it that here range is a smaller array as compared to criteria. ...
    (microsoft.public.excel.misc)
  • Re: AdvancedFilter syntax
    ... I think you'll have to use a real range for that criteria. ... And I'm not sure that this is quicker than reading the range into an array and ... Dim TempWks As Worksheet ... I'm underutilizing AdvancedFilter, and am hoping for a few pointers on how to ...
    (microsoft.public.excel.programming)
  • Re: Value deletion
    ... it selects with no criteria the needed field and the value gets ... # Bind variables should be used whenever possible. ... # the DBI error message or you won't have a clue what went wrong. ... # Because of the array, having criteria isn't a special case. ...
    (perl.dbi.users)
  • Re: Valid Collection?
    ... of VB and VBA in a Nutshell, ... I always try to use an array where possible, ... If I create a collection based on some criteria, ... Each' loops, and with other collections you can only do this loop ...
    (microsoft.public.scripting.vbscript)