criteria function using max

[Guest]

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

 

[Guest]

IF([Age]>50,20000,IF([Age]<50,15000))

 

[Guest]

IF([Age]>50,20000,IF([Age]<50,15000))

 

[Biff]

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

 

[Biff]

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

 

[Sandy Mann]

IF([Age]>50,20000,IF([Age]<50,15000))

No need for the second IF()

=IF(C1>50,20000,15000)

or without an IF()

=15000+(C1>50)*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(C1>50,20000,15000))

or

=MIN(F1,15000+(C1>50)*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

IF([Age]>50,20000,IF([Age]<50,15000))
--
Brevity is the soul of wit.

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

 

[Sandy Mann]

IF([Age]>50,20000,IF([Age]<50,15000))

No need for the second IF()

=IF(C1>50,20000,15000)

or without an IF()

=15000+(C1>50)*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(C1>50,20000,15000))

or

=MIN(F1,15000+(C1>50)*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

IF([Age]>50,20000,IF([Age]<50,15000))
--
Brevity is the soul of wit.

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

 

[Guest]

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)

IF([Age]>50,20000,IF([Age]<50,15000))

No need for the second IF()

=IF(C1>50,20000,15000)

or without an IF()

=15000+(C1>50)*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(C1>50,20000,15000))

or

=MIN(F1,15000+(C1>50)*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

IF([Age]>50,20000,IF([Age]<50,15000))
--
Brevity is the soul of wit.

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

 

[Guest]

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)

IF([Age]>50,20000,IF([Age]<50,15000))

No need for the second IF()

=IF(C1>50,20000,15000)

or without an IF()

=15000+(C1>50)*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(C1>50,20000,15000))

or

=MIN(F1,15000+(C1>50)*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

IF([Age]>50,20000,IF([Age]<50,15000))
--
Brevity is the soul of wit.

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

 

[Guest]

If client age is 50, what amount they can contribute?

 

[Guest]

If client age is 50, what amount they can contribute?