Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula?
If your 3000 figure is in B2, and your 2888 or 3700 in C2, use the formula
=MIN(B2,C2) -- David Biddulph "MythicZohar" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula?
MythicZohar;196885 Wrote: 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) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54247 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula?
On Jan 24, 9:17*am, Pecoflyer
wrote: 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 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) -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)*& allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile:http://www.thecodecage.com/forumz/member.php?userid=14 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=54247 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Need Help With Array Formula | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Array Formula | Excel Worksheet Functions |