 Search:

# Need to create function smiliar to DMAX

Asked By: Lucas    Date: Jan 07    Category: MS Office    Views: 1965

I'm having a really hard time with this one. I've looked for
examples and help online, and none seem to be complex enough to get
me to where I want to be.

I guess my first question is: Can you simply edit an already
existing function in Excel? This would be ideal, as I could pull up
the code for the DMAX function, and I'd be more than halfway there

The function I need should be similar to DMAX, except instead of
entering criteria at the end, I'd like to enter values, and it
should already know to start at value one and end at value two.
I'll give you a simple version of what I want to do:

I've got two columns. One has dates in chronological order. The
other has values - lets say they are flows. I want to be able to
search both columns, for the maximum flow, between two specified
dates. Could someone help me make this happen?

Share:

my first question  is: Can you simply edit  an already existing
function in Excel?"

No.

=MAX(INDIRECT("B" & MATCH(DATEVALUE("August 18, 2005"),A5:A41,1)+4+1 &
":B" & MATCH(DATEVALUE("September 16, 2005")-1,A5:A41,1)+4))

a megaformula.. ugh! My first attempt, I think I'll get something
slicker next week.

Dates were in A5:A41, most recent at the bottom

Values were in B5:B41

The formula includes dates  such as "September 16, 2005" but the dates
can be in any format that excel  recognises as a date.
You could dump 'Datevalue(~~)' and just have cell references to dates

It gives the maximum  flow BETWEEN (and not including) the two dates;
this was so it could handle missing dates in the column. (The '+1' and
'-1' in the formula compensate for what the Match function  returns.)

The '+4' (twice) in the formula is because the data starts at row 5
(Match would return 1 if a date before the first date was entered, but
this datum is at row 5.. the difference is +4). I kept the '+4' and
'+1' separate for better understanding and easier adjustment.
Note: I later found entering a date before the first date fails - more
work there.

Any upper case letter B within quotation marks is the reference to
column B where the flow values  are held.

A user defined function would probably be more robust, tidier and more
flexible. Perhaps next week...

Of course it gets better:

=LARGE((((A5:A41>=DATEVALUE("August 15,
2005"))*1)*((A5:A41<=DATEVALUE("August 13, 2005"))*1)*(B5:B41)),1)

entered as an ARRAY formula (Ctrl+Shift+Enter as you enter  it so that
curly braces appear round it).

Now you can control better the start  and end  dates; this time  it
INCLUDES the dates  specified because I've used <= and >=, but you can
No need to compensate for which row the data starts on, no fighting
with the Match function.
UDF next week perhaps

and perhaps better..

User Defined function  (UDF) called MaxFlow (code is at the end  of this
post)
Syntax:
=MaxFlow(A5:B41,1,2,"8/25/2005","9/12/2005")
or
=MaxFlow(A5:B41,1,2,L3,L4)
Where:

A5:B41 is the range which contain the dates  and values  but NOT the

1 is the column number of the above range containing the dates (their
sort order  is irrelevant, in fact they don't need to be sorted).

2 is the column number of the above range containing the values from
which the max must be obtained.

"8/25/2005" is the earlier date in the format that excel  can recognise
as a date. (This can also be a reference to a cell containing a date.)

"9/12/2005" is the later date in the format that excel can recognise
as a date. (This can also be a reference to a cell containing a date.)

This UDF has a cleaner way to enter  the formula on the sheet but it
isn't as efficient as the previous array formula (which can,
incidentally be simplified further:
=MAX(((A5:A41>=DATEVALUE("August 15,
2005"))*1)*((A5:A41<=DATEVALUE("August 24, 2005"))*1)*(B5:B41))
also entered as an array formula. I cocked up in the LARGE example in
the last post slightly by having the dates the wrong way round; the
earlier date should always be the first.
If I knew a one line way to multiply arrays in vba, it could be as
efficient.

Anyway that code:

Function MaxFlow(myData As Range, DatesColumn As Integer, _
ValuesColumn As Integer, FirstDate As String, SecondDate As String)
Application.Volatile
myarray = myData
MaxFlow = -1E+24
NoOfRows = myData.Rows.Count
For i = 1 To NoOfRows
If myarray(i, DatesColumn) >= DateValue(FirstDate) _
And myarray(i, DatesColumn) <= DateValue(SecondDate) Then
If MaxFlow < myarray(i, ValuesColumn) Then _
MaxFlow = myarray(i, ValuesColumn)
End If
Next i
End Function

If you don't know what to do with this code  then go to and follow
instructions at:

office.microsoft.com/.../HA010548461033.aspx

and when you reach point 3 in it, instead of where it says 'type the
code for your function', paste the block of code into it then carry on
with the rest of the instructions.

I've finally been able to give  these suckers a try, and I can't
get any of them to work. They all return "#VALUE!". Let me give you some
more specifics that may help:

The columns  of dates  and flows are not next to each other. Dates are in
column AC, and flows are in column AG. Also, the dates are actually times.
They are in increments of 15 minutes, over several days (it's a large

The UDF returns a value, but not the correct one. Do you think these formulas
can still work?

Unless I'm missing something, why won't the simpler MAX()
function do the job?

Example:

row ... AC ................. AG
1100 .. 5/5/05 2:00:00 PM .. 2,500
..
1824 .. 5/7/05 3:15:00 PM .. 1,700

and in any open cell, "enter" the formula
=MAX(AG1100:AG1200)

where "enter" means type "=MAX(", and then click
the cell of col AG (the flow) in the row
corresponding to the begin date, and drag to the row
of the end  date.

Granted, 724 rows is a bit of dragging, but how often
do you plan to do this?

If this is something you routinely plan to do, then
you could dedicate 3 cells, eg:
A1 = begin date
A2 = end date
A3 = max flow

where 1 and 2 are manually entered, and

where 3 contains a formula (perhaps one similar to what
Pascal is working on) -- or --

where 3 is simply populated when a button is pushed.
The button would trigger a sub that would use
the dates  contained in A1 and A2, then do a "maxflow search"
in col AG between (and including) the rows delimited by the 2 dates.

Didn't find what you were looking for? Find more on Need to create function smiliar to DMAX Or get search suggestion and latest updates.