Finding the Max value

[Frank Kabel]

Hi Mark
try
=SUMPRODUCT(MAX((A1:A1000=C1)*(B1:B1000)))

 

[sh0t2bts]

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

 

[sh0t2bts]

I can't get it to work it just returns 0.00?

I am using Excel 2000?

Any ideas?

Cheers

Mark

 

[Aladin Akyurek]

=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.

 

[Peo Sjoblom]

If these are supposed to be time formats, format custom as [ss.00],
if decimals format as general

 

[sh0t2bts]

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

 

[Frank Kabel]

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

 

[green78]

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),A16,4,FALSE

 

[sh0t2bts]

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

 

[Frank Kabel]

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