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. |
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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 02:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com