Using LARGE function with criteria

  • Thread starter Thread starter TT
  • Start date Start date
T

TT

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
 
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
 
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!
=====================================================


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
 
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 th
regular <Enter>, which will *automatically* enclose the formula in curl
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

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

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

Previous Posts In This Thread:

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

RE: Using LARGE function with criteria
d1=LARGE(IF((A1:A10=c1),B1:B10),n
text - column
numrics - column
c1 = text criteri
n - nt figur
it is an array formula. enter with ctrl+shift+ente
best wishe
sreedha

:

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 th
regular <Enter>, which will *automatically* enclose the formula in curl
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

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

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

RE: Using LARGE function with criteria
Perfect. Much appreciate

:

EggHeadCafe - Software Developer Portal of Choice
C# : Implement Data Access Layer independent of Physical Database Schema
http://www.eggheadcafe.com/tutorial...7fa-105527c433fc/c--implement-data-acces.aspx
 
When tailoring this same array formula to my spreadsheet I receive and "#num!" error. Any suggestions?



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!
=====================================================


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:

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

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

:

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!
=====================================================


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

RE: Using LARGE function with criteria
Perfect. Much appreciated

:

Text in Column A, numbers in Column B, and text criteria in C1
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!

EggHeadCafe - Software Developer Portal of Choice
..NET Beginner's Guide To UI, Business, Data Layers
http://www.eggheadcafe.com/tutorial...b340-2e8c8cefd9d7/net-beginners-guide-to.aspx
 
Did you enter the formula as an array?

=LARGE(IF(A1:A15=C1,B1:B15),1)

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.
 
Back
Top