Col A ------------------------Col B -------------------------------Col C -----
----------------Col D ---------------Col E
1 Date --------------------- Price ---------------------------- --
--------------------B/date ------------Yield Factor
2.05-FEB-07--------------100 -------------------------------- ------
----------------07-FEB-07--------- 2
3.06-FEB-07 -------------102 -------------------------------- ------
----------------13-FEB-07 -------- 1.5
4.07-FEB-07 -------------52 ----------------------------------- ----
---------------- 19-FEB--07 ------- 2
5.08-FEB-07 -------------50.50 ----------------------------------- -
--------------------
6.09-FEB-07 ------------- 50 ---------------------------------- ----
-----------------
7.12-FEB-07 ------------- 50.90 ----------------------------- -----
---------------
8.13-FEB-07 ------------- 34.50 ----------------------------- -----
----------------
9.14-FEB-07 ------------- 35 ------------------------------ ----
--------------------
10.15-FEB-07 -----------33 ---------------------------------- ----
-------------------
11.19-FEB-07 ---------- 16 ---------------------------------- ----
-------------------
12. 20-FEB-07 ---------- 16.40 ---------------------------- -----
-------------------

Normally C2

= Max(B2:B12) will give me high price.I have a bonus table in range D2:D6 .
Basing on this table I want to calculate high price in above range.The
criteria of calculation is,on 07-FEB_07 bonus of 1 unit is given for 1 unit.
Therefore on and from 07-FEB-07 value of price will be
07-FEB-07 --- 104 (52 multiplied by yield factor 2)
08-FEB-07 --- 101 (50.50 multiplied by Yield Factor 2)
09-FEB-07 ----100( 50 " " " " 2)
12-FEB-07 --- 101.80(50.90 " " " " 2)
Again on 13-FEB-07 bonus 1unit is given for every 2 units of holding.
Therefore on and from 13-FEB-07 the value of price will be
13-FEB-07 - 103.50 ( 34.50 multiplied by Yield factor 2 and 1.5)
14-FEB-07 - 105 ( 35 multiplied by yield factor 2 and 1.5)
15-FEB-07 - 99 ( 33 multipled by yield factor 2 and 1.5)
In this way basing on this calculation the value of prices in above range
will be
05-FEB-07 -100
06-FEB-07 - 102
07-FEB-07 - 104
08-FEB-07 - 101
09-FEB-07 - 100
12-FEB-07 - 101.80
13-FEB-07 - 103.50
14-FEB-07 - 105
15-FEB-07 - 99
19-FEB-07 - 96
20-FEB-07 - 98.40
So the highest price is 35 ( 14-FEB-07)
Highest price during 05-FEB-07 to 09-FEB-07 is 52(07-FEB-07)
Highest price during 15-FEB-07 to 20-FEB-07 is 33(15-FEB-07)
I am looking for a function that gives me above results, without creating a
helper column.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1