Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria function using max
I'm trying to set up a max function with two specific criteria.
In column C I have client Age In column F I have Employee contribution Criteria: If the client is above the age of 50, the max amount they can contribute is 20000 If the client is below the age of 50, the max they can contribute is 15000 also, in column G, I have employer contribution. I need a formula so that employers cannot contribute more than monthly gross income (column D/12) Thank you very much |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria function using max
IF([Age]50,20000,IF([Age]<50,15000))
-- Brevity is the soul of wit. "Scott" wrote: I'm trying to set up a max function with two specific criteria. In column C I have client Age In column F I have Employee contribution Criteria: If the client is above the age of 50, the max amount they can contribute is 20000 If the client is below the age of 50, the max they can contribute is 15000 also, in column G, I have employer contribution. I need a formula so that employers cannot contribute more than monthly gross income (column D/12) Thank you very much |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria function using max
If the client is above the age of 50
If the client is below the age of 50 What if the client *IS* 50? Biff "Scott" wrote in message ... I'm trying to set up a max function with two specific criteria. In column C I have client Age In column F I have Employee contribution Criteria: If the client is above the age of 50, the max amount they can contribute is 20000 If the client is below the age of 50, the max they can contribute is 15000 also, in column G, I have employer contribution. I need a formula so that employers cannot contribute more than monthly gross income (column D/12) Thank you very much |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria function using max
"Dave F" wrote in message
... IF([Age]50,20000,IF([Age]<50,15000)) No need for the second IF() =IF(C150,20000,15000) or without an IF() =15000+(C150)*5000 However, my reading of the OP's question is that the OP is actually looking for a MIN() function along with the IF() =MIN(F1,IF(C150,20000,15000)) or =MIN(F1,15000+(C150)*5000) For the employer's contribution I would suggest: =MIN(G1,D1/12) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Dave F" wrote in message ... IF([Age]50,20000,IF([Age]<50,15000)) -- Brevity is the soul of wit. "Scott" wrote: I'm trying to set up a max function with two specific criteria. In column C I have client Age In column F I have Employee contribution Criteria: If the client is above the age of 50, the max amount they can contribute is 20000 If the client is below the age of 50, the max they can contribute is 15000 also, in column G, I have employer contribution. I need a formula so that employers cannot contribute more than monthly gross income (column D/12) Thank you very much |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria function using max
The OP didn't specify what to do if Age = 50, but I am guessing he may be
working w/the U.S. 2006 401(k) limits. In which case, those 50 and older can contribute up to 20,000. To slightly change your formula: =MIN(F1,15000+(C1=50)*5000) "Sandy Mann" wrote: "Dave F" wrote in message ... IF([Age]50,20000,IF([Age]<50,15000)) No need for the second IF() =IF(C150,20000,15000) or without an IF() =15000+(C150)*5000 However, my reading of the OP's question is that the OP is actually looking for a MIN() function along with the IF() =MIN(F1,IF(C150,20000,15000)) or =MIN(F1,15000+(C150)*5000) For the employer's contribution I would suggest: =MIN(G1,D1/12) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Dave F" wrote in message ... IF([Age]50,20000,IF([Age]<50,15000)) -- Brevity is the soul of wit. "Scott" wrote: I'm trying to set up a max function with two specific criteria. In column C I have client Age In column F I have Employee contribution Criteria: If the client is above the age of 50, the max amount they can contribute is 20000 If the client is below the age of 50, the max they can contribute is 15000 also, in column G, I have employer contribution. I need a formula so that employers cannot contribute more than monthly gross income (column D/12) Thank you very much |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
criteria function using max
If client age is 50, what amount they can contribute?
"Scott" wrote: I'm trying to set up a max function with two specific criteria. In column C I have client Age In column F I have Employee contribution Criteria: If the client is above the age of 50, the max amount they can contribute is 20000 If the client is below the age of 50, the max they can contribute is 15000 also, in column G, I have employer contribution. I need a formula so that employers cannot contribute more than monthly gross income (column D/12) Thank you very much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
include criteria to 'rank based array function' | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Countif Function, complex criteria | Excel Worksheet Functions | |||
SumIf Function using multiple criteria | Excel Worksheet Functions |