Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why doesn't this formula work, please?
=IF(D26-F265,MIN(D26-F26,30),"0"). I also tried =IF(SUM(D26-F26)
5,MIN(D26-F26,30),"0"). But I keep getting "0", unless I enter a number higher than "10", which is confusing, as the quantative value here is 5. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why doesn't this formula work, please?
Your formula states *GREATER* then 5!
Maybe you actually want this: =IF(D26-F26=5,MIN(D26-F26,30),0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ed" wrote in message ... =IF(D26-F265,MIN(D26-F26,30),"0"). I also tried =IF(SUM(D26-F26) 5,MIN(D26-F26,30),"0"). But I keep getting "0", unless I enter a number higher than "10", which is confusing, as the quantative value here is 5. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why doesn't this formula work, please?
Ed,
The formula should be =IF(D26-F265,MIN(D26-F26,30),0) i.e no "" around the zero but that's an aside. The quantitative value is 5 so if D26-F26 is greater than 5 you get the Min(result of the subtraction,30), otherwise you get zero. In short the formula is doing exactly what your telling it to do and if that isn't what you want post back with the actual values in D26 & f26 and the answer you expect. Mike "Ed" wrote: =IF(D26-F265,MIN(D26-F26,30),"0"). I also tried =IF(SUM(D26-F26) 5,MIN(D26-F26,30),"0"). But I keep getting "0", unless I enter a number higher than "10", which is confusing, as the quantative value here is 5. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why doesn't this formula work, please?
On Jul 21, 4:19*pm, Mike H wrote:
Ed, The formula should be =IF(D26-F265,MIN(D26-F26,30),0) i.e no "" around the zero but that's an aside. The quantitative value is 5 so if D26-F26 is greater than 5 you get the Min(result of the subtraction,30), otherwise you get zero. In short the formula is doing exactly what your telling it to do and if that isn't what you want post back with the actual values in D26 & f26 and the answer you expect. Mike "Ed" wrote: =IF(D26-F265,MIN(D26-F26,30),"0"). *I also tried =IF(SUM(D26-F26) 5,MIN(D26-F26,30),"0"). *But I keep getting "0", unless I enter a number higher than "10", which is confusing, as the quantative value here is 5.- Hide quoted text - - Show quoted text - Mike, still no luck. This is a simple form I came up with for a prescription reimbursement benefit we've got at our company. You can send in amounts more than $5 (the copay) and up to $30. Column D is the actual amount, column F is the $5 copay, so the idea is if cell D minus cell F is greater than 5, SUM up to a maximum of $30. This formula worked when the copay was $10, but now that I've changed it to $5 and added the maximum of $30, it doesn't work except for amounts in column D of $10 or more. Anything lower doesn't regiser, I get a sum of 0. I've tried clearing the contents of the SUM column to make sure there isn't some residual ghost something or other, but even your formula doesn't work. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why doesn't this formula work, please?
Ed,
I knew my formula wouldn't work, a was simply pointing out that you should be using 0 and not "0". What you describe is what I expect to happen so lets approach this a different way D26=10 F26=5 what result do you expect? can f26 contain other values? If so provide examples D26=n1 F26=n2 expected result=? Mike "Ed" wrote: On Jul 21, 4:19 pm, Mike H wrote: Ed, The formula should be =IF(D26-F265,MIN(D26-F26,30),0) i.e no "" around the zero but that's an aside. The quantitative value is 5 so if D26-F26 is greater than 5 you get the Min(result of the subtraction,30), otherwise you get zero. In short the formula is doing exactly what your telling it to do and if that isn't what you want post back with the actual values in D26 & f26 and the answer you expect. Mike "Ed" wrote: =IF(D26-F265,MIN(D26-F26,30),"0"). I also tried =IF(SUM(D26-F26) 5,MIN(D26-F26,30),"0"). But I keep getting "0", unless I enter a number higher than "10", which is confusing, as the quantative value here is 5.- Hide quoted text - - Show quoted text - Mike, still no luck. This is a simple form I came up with for a prescription reimbursement benefit we've got at our company. You can send in amounts more than $5 (the copay) and up to $30. Column D is the actual amount, column F is the $5 copay, so the idea is if cell D minus cell F is greater than 5, SUM up to a maximum of $30. This formula worked when the copay was $10, but now that I've changed it to $5 and added the maximum of $30, it doesn't work except for amounts in column D of $10 or more. Anything lower doesn't regiser, I get a sum of 0. I've tried clearing the contents of the SUM column to make sure there isn't some residual ghost something or other, but even your formula doesn't work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to work in a 3-d reference with 2 work books | Excel Worksheet Functions | |||
Sorting the cells of a formula causes the formula to not work | Excel Worksheet Functions | |||
IF-then formula doesn't work | Excel Discussion (Misc queries) | |||
The same formula doesn't work again? | Excel Worksheet Functions | |||
A search for $ in a formula use to work now it does not work | Excel Discussion (Misc queries) |