2 formulas in Excel

[Mark2Aus]

I am designing a excel spreadsheet to generate daily signals related to the prices of wheat.
Rows represent the days and columns represent the commodity price recorded every 10 minutes.
For a Range D10:W10, for a particular day, I need to have 2 formulas written in AA10 and AB10 which would calculate:

1) After the commodity price hit the most recent Low, calculate the maximum number of points the price of the commodity rises from that last Low, without dropping any point until it starts to drop again (the "price run after the Low"); and

2) From there, calculate the maximum number of points the commodity price drops before starting to rise again (the "price rejection from the recovery").

Lets say we have the following daily data:

D E F G H I J K L M N O P
25 27 28 26 23 24 23 25 25 27 29 26 27

The first formula should return 6 (from the last Low of 23 in J10 the price rises without dropping to 29); and the second formula should return 3 ( after hitting 29 it then drops to 26 before climbing again to 27).
I can identify the last Low through the array formula
=MAX(IF(D10:W10=MIN(D10:W10),COLUMN(D10:W10),0)), but from there I could not come up with a formula which would measure the subsequent "price run" and "rejection".
Thanks for any help
Mark

 

[GlennUK]

This sounds like several postings I have read over the last couple of weeks, and I'd say that when you've got complex rules ( that may change in some way depending on requirements ), then the best way to get the result you want in to write a User Defined Function ( UDF ). This would involve writing VBA code. Do you have any experience in writing VBA for Excel?





Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

I second everything Glenn says.
I also note that your formula does not do what you say.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Mark2Aus]

1)No. I am not a programmer. I am afraid I don't have any experience writing VBA for Excel.
I am trying to develop a spreadsheet based on built-in functions even if I have to create another Sheet within the spreadsheet, manipulating the individual prices.
For example creating a sheet where each cell would be formed by the accumulated variation in prices from one time-slot to another, if the move is in the same direction.
Would this help? If not, how could I write a UDF?

2)What I meant is that with my formula I can identify the number of the column where the Low has happened and from there I thought I could calculate the price run.
Cheers
Mark