good morning,

i'm managing a worksheet that is organized as follows:

Columns A: Time
Cell B1: Default Time 9:10:00
Cell C1: Bin Time 00:05:00
Columns D: This formula(let's call time bin)
=CEILING(A2-$B$1;$B$1)/$B$1

I'd like to write a sub in vba that calculate the maximun whithin every
Time Bin.

thank you



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

dim myMax as double
myMax = application.max(worksheets("sheet1").range("d:d"))

(I'm not sure what "every time bin" means. This finds the maximum within column
D.)



ferrdav wrote:

good morning,

i'm managing a worksheet that is organized as follows:

Columns A: Time
Cell B1: Default Time 9:10:00
Cell C1: Bin Time 00:05:00
Columns D: This formula(let's call time bin)
=CEILING(A2-$B$1;$B$1)/$B$1

I'd like to write a sub in vba that calculate the maximun whithin every
Time Bin.

thank you

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

thank's dave, but my problem it's a bit more complex:

time bin it's a function that assess a number to those trades that are
in whithin a certain range of time, let's say 5 minutes.

since the number of trades (for a security) aren't the same for 5
minutes in 5 minutes, i need to isolate prices that were made in first
5 minutes of tdaes (time bin = 1) form those in the second five minutes
of trade (time bin=2) and so on... this' what time bin function does

what i need is to calculate the maximum price between those in time bin
1, and then the maximum in time bin 2 and so on...

i believe i should use a do..while loop, but i cannot do that...:(



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I'm not sure I understand, but if you have 1,2,3,... in the timebins column
(column B), you could use a formula like:

=MAX(IF(B1:B99=1,A1:A99))

A pivottable might work nicely, too. You can show the
max/min/average/sum/count/(lots of stuff) based on that timebins column.



ferrdav wrote:

thank's dave, but my problem it's a bit more complex:

time bin it's a function that assess a number to those trades that are
in whithin a certain range of time, let's say 5 minutes.

since the number of trades (for a security) aren't the same for 5
minutes in 5 minutes, i need to isolate prices that were made in first
5 minutes of tdaes (time bin = 1) form those in the second five minutes
of trade (time bin=2) and so on... this' what time bin function does

what i need is to calculate the maximum price between those in time bin
1, and then the maximum in time bin 2 and so on...

i believe i should use a do..while loop, but i cannot do that...:(

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson