Finding the Max value

  • Thread starter Thread starter Frank Kabel
  • Start date Start date
Hi All,

I have two columns of data the first has a list of extension numbers the
second have a time duraition in seconds, what I want to do if fine the
highest time duration for a given extension number.

In Cell C1, I Place the extension number I want Eg 6001 and the formula in
Cell D1 will show the highest time value from colum B "186" if I change the
value in cell C! to 6000 the value in cell D1 will change to 240.

How would I do this?

Many Thanks

Mark

A B
1 6000 236
2 6000 180
3 6001 126
4 6000 240
5 6002 156
6 6001 186
 
I can't get it to work it just returns 0.00?

I am using Excel 2000?

Any ideas?

Cheers

Mark
 
=MAX(IF($A$1:$A$100=C1,$B$2:$B$100))

which you need to confirm with control+shift+enter insteaf of just with
enter.
 
If these are supposed to be time formats, format custom as [ss.00],
if decimals format as general
 
Still no Match,

It keeps coming back with "0" no matter what function I use from this
thread???

Not happy I'm pulling my hair out!!!

Mark
 
Hi
did Peo's suggestion work for you? This was also my guess that you have
to format the resulting cell with an approbiate time format
 
Hi, I have a bit clumsy decision for you, but I think it will do th
job:

How about moving your data starting from column B:

B C D
1 6000 236
2 6000 180
3 6001 126
4 6000 240
5 6002 156
6 6001 186

Let in cell E1 be the extention number you want the maximum for:

now in cell A1 type this function:

=IF(C1=$E$1,VALUE(C1&D1),"")

Now stretch A1 till the end of your data.

Cell F1 will be the result, there you have to type:

=VLOOKUP(MAX(A1:A6),A1:D6,4,FALSE
 
Na nether have worked,

I think I will look at doing a bit of VBA and get a macro to get me the
value, it's only a few lines and shouldn't slow the report down too much.


Many Thanks for you help guys

Mark
 
Hi Mark
the formulas should work. If you like, email me your spreadsheet
(frank[dot]kabel[at]freenet[dot]de) and I'll have a look at it
 
Back
Top