Max Value of a cell

C

cwilliams

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!
 
C

cwilliams

I tried

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

And this did not work either
 
B

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)
 
C

cwilliams

great thanks!!!!!

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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
C

cwilliams

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)
 
R

Ron Coderre

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
 
R

Ron Coderre

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

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

XL2002, WinXP
 
C

cwilliams

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.
 
G

Gord Dibben

Try this........

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


Gord Dibben MS Excel MVP
 
T

Toppers

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)
 
C

cwilliams

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.
 
T

Toppers

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
 
Top