Function Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a sample of the spreadsheet that I am working with below, what I need
to do is put together a function or formula that will flag the column with
the highest ODM reading for each unit. Any ideas on a way that I could do
that - once the row was flagged on could filter to get the report that I want
- each unit listed with only the highest ODM.

unit Miles ODM
1 10 50000
1 20 40000
3 100 10
3 150 150
3 200 250000
 
One way (Array-entered: CTRL-SHIFT-ENTER or cmd-RETURN):

=IF(C2=MAX(($A$2:$A$100=A2)*$C$2:$C$100),"Max","")

copy down.

Better (but not one of your options):

A Pivot Table with Unit in the Row field and Max of ODM in the Data
field.
 
One way ..

Put in D2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=AND(C2=MAX(IF(A$2:A$100=A2,C$2:C$100)),C2<>"")
Copy D2 down, then autofilter on col D for TRUE.
Adapt the ranges to suit.
 
I have a sample of the spreadsheet that I am working with below, what I need
to do is put together a function or formula that will flag the column with
the highest ODM reading for each unit. Any ideas on a way that I could do
that - once the row was flagged on could filter to get the report that I want
- each unit listed with only the highest ODM.

unit Miles ODM
1 10 50000
1 20 40000
3 100 10
3 150 150
3 200 250000
Click to expand...

=IF(SUMPRODUCT(MAX(($C$2:$C$100)*($A$2:$A$100=A2)))=C2,C2,"")

Ken Johnson
 
Erika,
Another idea. If you want the maximum values to be highlighted, use the
formulas suggested by various respondents to your question and get column C
conditionally formatted. Only the maximum values will be highlighted. (
Format >Conditional formatting -- in the dialogue box that pops up select
"Formula is " and copy the formula you like and indicate the formatting you
want by pressing format button and selecting a color your choice.
 
Back
Top