find max from a range with a greater than value
Hi i have gone through the different solutions but failed to get the solution for my problem. I have a table of data having date name and salary. Each salary revision will be added to the table as a new row and i need to find the salary of a person on a particular date. the result should be the salary of the particular employee on the maximum date less than the particular date. I am attaching the data here. I appreciate for you kind help on this.
EffectiveDate Name Salary
01-Jan-2012 Jason 10000
01-Jan-2012 Robin 12500
01-Jan-2012 Paul 11000
01-Mar-2012 John 15000
01-Mar-2012 Paul 11500
01-Mar-2012 Robin 15000
01-Jun-2012 John 20000
01-Jun-2012 Paul 12500
01-Jun-2012 Jason 15000
DMAX is what you need
If I understood you correctly, you want to see max salary for particular employee before specified date. For example, if you want Paul's maximum salary before 01-Jun-2012 (the last raise), result should be 11.500 (as at 01-Mar-2012) - because it is higher than 11000 (as at 01-Jan-2012).
If this is the case, DMAX function is solution.
1. Copy column names (exactly the same) above table with data and leave several blank rows.
In "Effective date" type "<01-Jun-2012" (less than 01-Jun-2012), and in "Name" enter employee's Name. Thus, you specify conditions about date less than 01-Jun-2012 and particular employee Name.
2. Now, in another cell insert DMAX function, like this:
Database: select your table, including column names
Field: Select column Salary or just column name ("Salary").
Criteria: select the range with criteria (column names copied above the table with data and row with conditions).
3. Result should be maximum salary for particular employee before particular date.
If you wish, I can send an example by e-mail.