Max Value of a cell
 

I hit another road block with my formula.

=IF(I19="yes",(((D15/52)*0.6)),0)

The max value can only be $1,000. How do I tell it if D15/52*60% is more
then $1,000 only enter $1,000. If the value is less then $1,000 then enter
the number from the formula. If I19 is no then 0 still needs to be used.

Thanks again to everyone!

Max Value of a cell
 

I tried

=IF(I19="yes",0)*AND(I20<1000,(D15/52)*0.6,"1000")

And this did not work either

Max Value of a cell
 

=IF(I19="yes",MAX(1000,D15/52*0.6),0)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"cwilliams" wrote in message
...
I tried

=IF(I19="yes",0)*AND(I20<1000,(D15/52)*0.6,"1000")

And this did not work either

Max Value of a cell
 

great thanks!!!!!

"Bob Phillips" wrote:

=IF(I19="yes",MAX(1000,D15/52*0.6),0)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"cwilliams" wrote in message
...
I tried

=IF(I19="yes",0)*AND(I20<1000,(D15/52)*0.6,"1000")

And this did not work either

Max Value of a cell
 

I entered that formula and it is not calculating if the value is under 1000
only if it is over 1000. Maybe the 1000 is in the wrong place?

=IF(I19="yes",MAX(1000,D15/52*0.6),0)

Max Value of a cell
 

Change MAX to MIN

Hope I help you

"cwilliams" wrote:

I entered that formula and it is not calculating if the value is under 1000
only if it is over 1000. Maybe the 1000 is in the wrong place?

=IF(I19="yes",MAX(1000,D15/52*0.6),0)

Max Value of a cell
 

Change MAX to MIN

"cwilliams" wrote:

I entered that formula and it is not calculating if the value is under 1000
only if it is over 1000. Maybe the 1000 is in the wrong place?

=IF(I19="yes",MAX(1000,D15/52*0.6),0)

Max Value of a cell
 

Cindy:

MAX should be MIN
I make that mistake all the time : \

Try this:
=IF(I19="yes",MIN(1000,D15/52*0.6),0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"cwilliams" wrote:

I entered that formula and it is not calculating if the value is under 1000
only if it is over 1000. Maybe the 1000 is in the wrong place?

=IF(I19="yes",MAX(1000,D15/52*0.6),0)

Max Value of a cell
 

Sorry about the "Cindy" (assuming your name is NOT Cindy)
I have no idea where that came from.

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Cindy:

MAX should be MIN
I make that mistake all the time : \

Try this:
=IF(I19="yes",MIN(1000,D15/52*0.6),0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"cwilliams" wrote:

I entered that formula and it is not calculating if the value is under 1000
only if it is over 1000. Maybe the 1000 is in the wrong place?

=IF(I19="yes",MAX(1000,D15/52*0.6),0)

Max Value of a cell
 

That is not it either. If the cell is "yes" and the amount is less then 1000
then it needs to used the formula D15/52*.6 and if the amount is over 1000
then it should just put 1000 in the cell. If the cell is "no" then it should
not do anything but put 0 in it.

Not sure if I am explaining this too well.

"Nobody" wrote:

Change MAX to MIN

Hope I help you

"cwilliams" wrote:

I entered that formula and it is not calculating if the value is under 1000
only if it is over 1000. Maybe the 1000 is in the wrong place?

=IF(I19="yes",MAX(1000,D15/52*0.6),0)

Max Value of a cell
 

Try this........

=IF(I19="yes",MIN(1000,((D15/52)*0.6)),0)


Gord Dibben MS Excel MVP

On Tue, 29 Aug 2006 09:35:02 -0700, cwilliams
wrote:

I tried

=IF(I19="yes",0)*AND(I20<1000,(D15/52)*0.6,"1000")

And this did not work either

Max Value of a cell
 

this will give you what you want...

If D15/52*0.6 < 1000, the answer will be D15/52*0.6, otherwise it will be
1000

if I19="no", result will be 0

=IF(I19="yes",MIN(1000,D15/52*0.6),0)

"cwilliams" wrote:

That is not it either. If the cell is "yes" and the amount is less then 1000
then it needs to used the formula D15/52*.6 and if the amount is over 1000
then it should just put 1000 in the cell. If the cell is "no" then it should
not do anything but put 0 in it.

Not sure if I am explaining this too well.

"Nobody" wrote:

Change MAX to MIN

Hope I help you

"cwilliams" wrote:

I entered that formula and it is not calculating if the value is under 1000
only if it is over 1000. Maybe the 1000 is in the wrong place?

=IF(I19="yes",MAX(1000,D15/52*0.6),0)

Max Value of a cell
 

Cell I19 is either "Yes" or "No"
Cell D15 has an annual salary
Cell I20 has a max weekly amount of 1000 based on D15 (d15/52)

Number I am trying to calculate is the weekly benefit amount based on I20
(d15/52)*60%

=IF(I19="yes",MIN(1000,D15/52*0.6),0)

Hope this explains it a lillte better.

Max Value of a cell
 

I20 doesn't appear anywhere in your formula (to add to the confusion) but you
say it has a maximum of 1000 based on D15/52; so if D15=62,400 then
D15/52=1200 which exceeds your maximum of 1000.

Are you saying compare 1200 with 1000, choose the smallest i.e. 1000 and
multiply this by 0.6?

if so, this will be the formula

=IF(I19="yes",MIN(1000,D15/52)*0.6,0)

if D15=52,100 result is 600 (1000*0.6)

if D15=50,000 result is 576.92 (50000/52)*0.6

"cwilliams" wrote:

Cell I19 is either "Yes" or "No"
Cell D15 has an annual salary
Cell I20 has a max weekly amount of 1000 based on D15 (d15/52)

Number I am trying to calculate is the weekly benefit amount based on I20
(d15/52)*60%

=IF(I19="yes",MIN(1000,D15/52*0.6),0)

Hope this explains it a lillte better.