I need help to find...

[panosmgr]

In fact I need an "excel brain" to help me for some calculations.


I have in excel :

DATE COUNTRY RATE1 RATE2
COL(A) COL(B) COL(C) COL(D)
3 GER 1.50 2.30
3 FRA 1.15 6.50
4 GER 1.20 6.00
4 GER2 1.40 5.50
4 IT1 1.80 3.60
6 IT2 3.50 2.11
7 POL 4.10 1.75
7 SP1 1.56 8.90
9 SP2 2.70 4.50



I want to add a formula in cell E1 that shows me how many times th
lowest rate of colC or colD appears from the 1st date of the mont
until... yestrday (in above example ..until 2) for selection GER.
colA are numbers now, but maybe in future I change to date format.
The formula must recognize itself which rate (from C1 and D1 are th
lowest).


I looking desparately for any help

Thanks
ko

 

[BrianB]

This seems to work OK for me. You will obviously need to adapt it.

The basic idea is to set a lookup range in the data based on the day in
cell A12 and use MIN() to find the minimum value.

Your data in cells A211
Day Number (as in column A) in cell A12
Number 10 in formula refers to last row number of data

Copy /pasteFormula :


=MIN(OFFSET($A$2,MATCH(A12,$A$2:$A$11,1),2,10-MATCH(A12,$A$2:$A$11,1),2))