Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Get largest number in a Coumn where cell =?

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
runnerjp2005
Asked:
runnerjp2005
1 Solution
 
Angelp1ayCommented:
Surely the largest value is 1?

Could you perhaps post the Excel so I understand better?
0
 
Rob HensonIT & Database AssistantCommented:
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
 
byundtCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
runnerjp2005Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
NO POINTS FOR THIS.

Follow byundt's instructions. In my words

Select the cell
Press F2
Press Shift-Ctrl-Enter
0
 
runnerjp2005Author Commented:
It worked thank!!!
0
 
Angelp1ayCommented:
Another excellent learning thread! Also thanks, never knew about array formulas before this :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now