Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TT TT is offline
external usenet poster
 
Posts: 32
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Using LARGE function with criteria

To extract the largest nth figures for a given text criteria, use the following formula:
  1. Replace "criteria" with the actual text criteria that you want to use.
  2. Replace "B2:B10" with the range of numeric figures that you want to extract the largest nth figures from.
  3. Replace "n" with the nth largest figure that you want to extract.
  4. Use the following formula:

    Formula:
    =IFERROR(LARGE(IF(A2:A10="criteria",B2:B10),n),"No data"

Note that this formula is an array formula, so you need to press Ctrl + Shift + Enter instead of just Enter to enter the formula.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 229
Default 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:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Using LARGE function with criteria

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" wrote in message
...
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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TT TT is offline
external usenet poster
 
Posts: 32
Default Using LARGE function with criteria

Perfect. Much appreciated

"yshridhar" wrote:

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:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help! How do I search for multiple criteria in a large spreadsheet tdub4 Excel Discussion (Misc queries) 2 October 26th 07 09:02 PM
Criteria governing "LARGE" function M.A.Tyler Excel Discussion (Misc queries) 5 August 21st 07 07:30 PM
Large Based on Criteria Peanut Excel Worksheet Functions 4 July 26th 07 08:06 PM
Large function with multiple criteria Adeline Excel Discussion (Misc queries) 2 September 8th 06 05:33 PM
dsum returns 0 if criteria range too large JT Spitz Excel Worksheet Functions 1 March 2nd 06 12:51 AM


All times are GMT +1. The time now is 05:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"