Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
up to 7 functions? | Excel Worksheet Functions |