Array Formula?

[MythicZohar]

I am trying to have excel return a certain value based on a series of
date ranges.

I want the value to return true or a specific number if it returns a
number less than the max, but if greater than a max number for the
period I want it to return only the max number.

An example:

01/01/2008-12/31/2008=3000,

01/01/2009-12/31/2009=3600.

So lets say the persons salary or wage for 2008 was 2888.00, then I
would want the formula to return 2888.00.

But if the salary for 2008 was 3700.00 I would want the value to
return 3000.

Can anyone suggest anything?

Thanks in advance

 

[David Biddulph]

If your 3000 figure is in B2, and your 2888 or 3700 in C2, use the formula
=MIN(B2,C2)

 

[Pecoflyer]

I am trying to have excel return a certain value based on a series of
date ranges.

I want the value to return true or a specific number if it returns a
number less than the max, but if greater than a max number for the
period I want it to return only the max number.

An example:

01/01/2008-12/31/2008=3000,

01/01/2009-12/31/2009=3600.

So lets say the persons salary or wage for 2008 was 2888.00, then I
would want the formula to return 2888.00.

But if the salary for 2008 was 3700.00 I would want the value to
return 3000.

Can anyone suggest anything?

Thanks in advance

If your salary is in A1 try =MIN(A1,3000

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[MythicZohar]

MythicZohar;196885 Wrote:
Thanks for the reply. however I am not sure if I presented the question correctly. The date ranges will not be represented on the sheet per se but only referenced by the formula to arrive at a result depending on the date of incident.

I will need the ability to have the formula reference a range of
dates based on a max for each date of incident and return and answer.

Here's and an example:

Lets say I have 3 entries
Name date of incident Average Monthly Wage
John Doe 01/01/2008 4333.
Jane Doe 01/01/2003 2888
Sally Doe 01/01/2009 3600


In the example above I would need to have the average monthly wage
column reference a range or table that included a series of date
ranges that set a max average monthly wage depending on the date
range which coincides with the date of incident, and return the actual
wage if not greater than the max for the period


below is an example of the table the formula would reference

if date of injury is:

01/01/2008-12/31/2008 max average monthle wage = 1385
01/01/1979-12/31/2003 max average monthly wage = 2000
01/01/2009-12/31/2009 max average monthly wage=36000

etc

Thanks again