?
Solved

Get largest number in a Coumn where cell =?

Posted on 2013-12-12
7
Medium Priority
?
320 Views
Last Modified: 2013-12-13
Hello,

I would like to get the Largest value from column O where the cell = 1.

I have tried =LARGE(Result!'O:O = 1', 1) but it does not work??

Any help would it be great!
0
Comment
Question by:runnerjp2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39714436
Surely the largest value is 1?

Could you perhaps post the Excel so I understand better?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39714515
It looks like you might need the DMAX function.

=DMAX(Data,Header,Criteria)

Data - Data range to be assessed
Header - Header of column from which you want the result
Criteria - Header and criteria required

Criteria will be in a small range (at least 1 column and 2 rows) of its own with the same header in your case as the column from which you are checking for the number 1 and the value 1 in the cell below it.

Thanks
Rob H
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39715712
I'm guessing that what you want to do is search say column B for cells containing 1. On those rows, look for the largest value in column O. If so, consider an array formula like:
=MAX(IF(B2:B1000=1,O2:O1000,""))

To array-enter the formula:
1.  Click in the formula bar
2.  Hold the Control and Shift keys down
3.  Hit the Enter key, then release all three keys
Excel should respond by putting curly braces { } surrounding the formula. If not (or if you see #VALUE! error value), then repeat steps 1-3.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:runnerjp2005
ID: 39716069
I have tried the above and I still get the #Value! error.... I repeated it a few time so i attached the sheet on here to check i did it right
J.P.B.S.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39716077
NO POINTS FOR THIS.

Follow byundt's instructions. In my words

Select the cell
Press F2
Press Shift-Ctrl-Enter
0
 

Author Closing Comment

by:runnerjp2005
ID: 39716274
It worked thank!!!
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39716556
Another excellent learning thread! Also thanks, never knew about array formulas before this :)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question