Can you help please...

[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

 

[Jerry W. Lewis]

Could you explain a bit more about the coding involved in column A?
Depending on your location relative to the international date line, you
posted the question on either August 22 or August 23, but said that the
coding for yesterday is 2.

It is also unclear what you mean by "... how many times the lowest rate
of colC or colD appears ... for selection GER." Are you looking for the
lowest rate from either column (1.15) which may not occur at all for
GER, the lowest rate from each column (1.15 for C and 1.75 for D) which
again may not occur at all for GER, the lowest rate for GER in column C
(1.2) and the lowest rate for GER in column D (2.3) and counting the
possible number of duplicate entries, ...

When you refer to "GER", does the value of "GER2" also meet your criteria?

What answer do you expect from the posted data?

Since the meaning of your question is unclear, this formula may not be
what you want, but it may give you some ideas. It asks for how many
rows do you simultaneously have the following conditions:

- The number in column A is < yesterday's day of the month (22, at the
time I am posting from the east coast of the USA)

- The first three characters of the country code in column B is "GER"
(note "GER2" satisfies this)

- Either
--- the value in column C is the smallest of all values in column C
regardless of country or date, or
--- the value in column D is the smallest of all values in column D
regardless of country or date.

The formula is

=SUMPRODUCT( (A2:A10<DAY(TODAY())) *(LEFT(B2:B10,3)="GER")
*((C2:C10=MIN(C2:C10))+(D210=MIN(D210))) )

which returns zero, because the minimums of columns C (3-FRA) and D
(7-POL) occur for different countries than GER.

Jerry

 

[panosmgr]

Dear Jerry,

forgive my unknowledge...
I try to be more clear now.


Here is my example again:
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 need to create a formula in colE :

search all database until today (NOT including today) and count th
times that smallest value (C1 or D1????Only from C1 and D1 not colC an
colD) appears in GER.

(GER is not the same as GER2).

Can you help me now???

Thank

 

[Jerry W. Lewis]

MIN(C1,D1) is the smaller of the values in C11. You would use a
formula similar to what I provided previously, but I still do not fully
understand your question, or what value you would expect to get back
from the example data that you posted.

Jerry