| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
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))+(D2  10=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 < wrote: > 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 the > lowest rate of colC or colD appears from the 1st date of the month > 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 the > lowest). > > > I looking desparately for any help > > Thanks > kol > > > --- > Message posted from http://www.ExcelForum.com/ > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
MIN(C1,D1) is the smaller of the values in C1
1. You would use aformula 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 panosmgr < wrote: > 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 the > times that smallest value (C1 or D1????Only from C1 and D1 not colC and > colD) appears in GER. > > (GER is not the same as GER2). > > Can you help me now??? > > Thanks > > > --- > Message posted from http://www.ExcelForum.com/ > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
