Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

already.

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?

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

elsewhere on the sheet instead.

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

adjust that to taste.

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

column headers of same.

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

spreadsheet).

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.

Related Topics:

- Function needed to split the content of a cell intomany cells
- Function needed to split the content of a cell into many cells
- Need help with "inputbox" function
- Need Help for Macro to Perform Same Function Only on Certain Sheets
- Wait function and Invisible function
- Wait function and Invisible function
- Create Excel Function with Visual Basic
- Need help w/creating macro to delete rows
- to create a program that will create a folder and file name
- Creating a new file that keeps the macro that creates it
- How can I call the function TotalPrice within another function?
- help on functions
- Problems with XL's 'MATCH' function
- How to call a JNI function in C
- custom Sum function?
- User defined functions not recognized
- "Min 4" function?
- New MAX function?
- Populate Formula(Function) in rows
- defining variables in function
- help requested for using excel's built-in function in coding
- Linest Function
- user defined functions
- Call C function through Java
- Column Headings using getColumnName function