Max Value of a cell

[Guest]

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!

 

[Guest]

I tried

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

And this did not work either

 

[Bob Phillips]

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


--
HTH

Bob Phillips

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

 

[Guest]

great thanks!!!!!

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


--
HTH

Bob Phillips

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

 

[Guest]

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)

 

[Guest]

Change MAX to MIN

Hope I help you

 

[Guest]

Change MAX to MIN

 

[Guest]

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

 

[Guest]

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

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

XL2002, WinXP

 

[Guest]

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.

 

[Gord Dibben]

Try this........

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


Gord Dibben MS Excel MVP

 

[Guest]

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)

 

[Guest]

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.

 

[Guest]

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