Large function with if statement

[caroline vincent]

Hi,
I have the following problem, any help appreciated.
Column A: hospital name
Column B: private or public
Column C: number of patient in that hospital

I am using the function Large(A1:A1000,1); Large(A1:A1000,2);
Large(A1:A1000,3); etc in column D to rank the hospital according to
the number of patients.
Now I would like to do the same based on the choice Private or Public:
if we choose Private in E1
If (B1:B1000=E1,Large(A1:A1000,1), etc I also tried with a sumproduct
but did not work either

Any idea would be welcome. Cheers
Caroline

 

[Jim Cone]

Sort by Column B and Column C and they will be automatically ranked for you.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html

..
..

"caroline vincent" <[email protected]>
wrote in message
Hi,
I have the following problem, any help appreciated.
Column A: hospital name
Column B: private or public
Column C: number of patient in that hospital

I am using the function Large(A1:A1000,1); Large(A1:A1000,2);
Large(A1:A1000,3); etc in column D to rank the hospital according to
the number of patients.
Now I would like to do the same based on the choice Private or Public:
if we choose Private in E1
If (B1:B1000=E1,Large(A1:A1000,1), etc I also tried with a sumproduct
but did not work either

Any idea would be welcome. Cheers
Caroline

 

[Don Guillett Excel MVP]

Sort by Column B and Column C and they will be automatically ranked for you.
--
Jim Cone
Portland, Oregon  USAhttp://www.contextures.com/excel-sort-addin.html

.
.

"caroline vincent" <[email protected]>
wrote in messageHi,
I have the following problem, any help appreciated.
Column A: hospital name
Column B: private or public
Column C: number of patient in that hospital

I am using the function Large(A1:A1000,1); Large(A1:A1000,2);
Large(A1:A1000,3); etc in column D to rank the hospital according to
the number of patients.
Now I would like to do the same based on the choice Private or Public:
if we choose Private in E1
If (B1:B1000=E1,Large(A1:A1000,1), etc I also tried with a sumproduct
but did not work either

Any idea would be welcome. Cheers
Caroline

You may want to modify this ARRAY formula(enter using ctrl+shift
+enter)

=MAX(IF((G1:G4="Japan"),F1:F4))

 

[DCG-jaeson]

You may want to modify this ARRAY formula(enter using ctrl+shift
+enter)

=MAX(IF((G1:G4="Japan"),F1:F4))- Hide quoted text -

Hello guys,

I'd been checking this post from caroline because it so very usefull
in many kinds of reports, and I also dont know how can be made. The
formula from Sir Don is working only for the Topmost per criteria, how
about the second, third or the others? Can we use Large but dont know
how like carol. :-(

~I also trying to figure what's the combinations of formula exactly
needed in this problem.

Advance happy holiday to all!!!
~jaeson

 

[Don Guillett Excel MVP]

Hello guys,

I'd been checking this post from caroline because it so very usefull
in many kinds of reports, and I also dont know how can be made. The
formula from Sir Don is working only for the Topmost per criteria, how
about the second, third or the others? Can we use Large but dont know
how like carol. :-(

~I also trying to figure what's the combinations of formula exactly
needed in this problem.

Advance happy holiday to all!!!
~jaeson

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[DCG-jaeson]

Hi Carol,

I know now the formula to solve your problem...

Put "Hospital Type" as Header on Cell D1 then Under it is for the
Hospital Criteria (Cell D2). Same in Column E for your Ranking
Criteria. Column F for the result, Type "Result" in F1 as header.

~> put this in your Cell "F2",
=INDEX(B2:B1000,LARGE(IF(A2:A1000=D2,ROW(B2:B1000)-ROW(B2)+1),E2))

Note: if #NUM! appeared just press F2 then Ctrl+Shift+Enter to refresh
the result.

Hope I Helped.

Happy Holiday!
~jaeson