Ideas on an Excel formula from the spreadsheet gurus out there. 3

[Dandas]

Does anyone know of a way to find the Maximum of a range depending on a certain criteria. Ideally I wish there were a MAXIF formula that worked in the same way as a SUMIF.

I need to be able the find the max based on a series of records by date. Eg. Find the MAXIMUM "X" on July 01. At the moment I am using a pivot table but if I can find a way to create a maxif it would be fantastic.

Thanks
Dandas

 

[ETID]

copy this function into a module...(alt+f11,...highlite the spread sheet name in the left pane, then insert module)

then the usage is....

=Maxif(the date col,the X col, "07/01/02&quot

This function will only be available in that workbook.

If you need it available to all workbooks the it needs to be added to the personal.xls or save in a blank workbook as an AddIn....


'-----------------------------------------------

Function Maxif(InRange1, Inrange2, criteria1)
If criteria1 = "" Then Beep: End
Dim r As Long
Dim lastr1 As Long
Dim lastr2 As Long
Dim in_range1(70000)
Dim in_range2(70000)

Set SubSetRange1 = _
Intersect(InRange1.Parent.UsedRange, InRange1)
Set SubSetRange2 = _
Intersect(Inrange2.Parent.UsedRange, Inrange2)
TheMax = 0
For Each cell In SubSetRange1
in_range1(r) = cell.Value
r = r + 1
Next cell
lastr1 = r: r = 0
For Each cell In SubSetRange2
in_range2(r) = cell.Value
r = r + 1
Next cell
lastr2 = r: r = 0
If lastr1 <> lastr2 Then Beep: MsgBox (&quot;Both ranges must have equalRows! &quot: End

Do While r < lastr1
If UCase(in_range1(r)) = UCase(criteria1) And in_range2(r) > TheMax Then TheMax = in_range2(r)
r = r + 1
Loop
Maxif = TheMax

End Function

 

[rmikesmith]

Dandas,

You're in luck! You can use DMax, one of the built-in database functions. The syntax is

DMax(Database,Field,Criteria)

where Database is the range of your table of information, including headings, Field is the column (field) you want to operate on, as either a heading name in double quotes or column index, and Criteria is a range that includes a field heading and expressions that serve as criteria. Let's say you had a database of stocks: Stock Symbol in Col A (&quot;Stock&quot, Closing price in Col B (&quot;Price&quot and Date in Col C (&quot;Date&quot; data in rows 2-20. Place &quot;Date&quot; in cell E1 and Jul 01 in E2 (for example). To find the maximum stock price for Jul 01 your formula would be

=DMax(A1:C20,"Price",E1:E2)

See the online help for more detail and examples.

HTH
Mike

 

[RITec]

Have you tried using DMAX?

Example:
A B C D
1 Datein Sales DMAX Datein
2 5/1/2002 667 781 5/1/2002
3 5/10/2002 421
4 5/15/2002 368
5 5/1/2002 781
6 6/10/2002 777
7 5/1/2002 141
8 5/10/2002 958

Formula in C2 &quot;=DMAX(A1:B10,&quot;Sales&quot;,D45)&quot;



David

 

[ETID]

Hey, rmikesmith


Nice!....

 

[rmikesmith]

Thanks ETID.

RiTec must have posted right after I did.

BTW, I have to thank Dale Watson for championing the use of Excel's Database functions.


Regards,
Mike

 

[Dandas]

Perfect thanks for your help guys.

A Star each.

 

[xlbo]

Just as a bit of extra knowledge if anyone didn't know, you can use MAX in an array formula:
=MAX((A1:A100=DATEVALUE(&quot;01/07/2001&quot)*(B1:B100))
use ctrl+shift+enter to get the {} around the formula to indicate an array formula

It's a bit complicated 'cos of the use of dates but works nicely....ok, ok, ok, I just like array formulae - I think they're pretty and don't require any criteria areas
You could also use a reference range with the date in so you could have a setup like thus
A B
Date Max Val
01/07/01 =MAX((sheet1!A1:A100=A2))*(sheet1!B1:B100))
02/07/01 =MAX((sheet1!A1:A100=A3))*(sheet1!B1:B100))
03/07/01 =MAX((sheet1!A1:A100=A4))*(sheet1!B1:B100))

HTH Rgds
~Geoff~