Large function with if statement

  • Thread starter Thread starter caroline vincent
  • Start date Start date
C

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
 
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
 
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
Click to expand...

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

=MAX(IF((G1:G4="Japan"),F1:F4))
 
You may want to modify this ARRAY formula(enter using ctrl+shift
+enter)

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


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
 
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
Click to expand...

"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."
 
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
 
Back
Top