| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
cell formulae giving negative result.
Hi,
I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
cell formulae giving negative result.
Firstly there is an error in the formula -
IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) IF(C2=49.99,39.99*0.06,IF(C2<49.99,(C2-10)*0.06)) Now, to answer the question. You can't stop the formula returning a negative number because it is a mathematical function. You can, however, change what it does when it gets a negative number. What do you want to happen when the formula returns a negative number? Regards. Bill Ridgeway Computer Solutions "BrianC500" wrote in message ... Hi, I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
cell formulae giving negative result.
what do you want the result to be if C2 < 10?
"BrianC500" wrote: Hi, I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
cell formulae giving negative result.
The second IF function (nested) isn't necessary since if C2 failed to meet
the first criterion where C2=49.99 then it is mandated to be less than 49.99. Also, since 39.99*0.06 is a hard number, the second of the two formula options below lists this number (2.399) instead: =If(C2=49.99, 39.99*0.06, Max(C2-10, 0)*0.06) =IF(C2=49.99,2.399,MAX(C2-10,0)*0.06) Regards, Greg "BrianC500" wrote: Hi, I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
cell formulae giving negative result.
Hi Bill,
Thanks for the reply. I would like the fomula to return a figure of zero. Regards Brian "Bill Ridgeway" wrote: Firstly there is an error in the formula - IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) IF(C2=49.99,39.99*0.06,IF(C2<49.99,(C2-10)*0.06)) Now, to answer the question. You can't stop the formula returning a negative number because it is a mathematical function. You can, however, change what it does when it gets a negative number. What do you want to happen when the formula returns a negative number? Regards. Bill Ridgeway Computer Solutions "BrianC500" wrote in message ... Hi, I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
cell formulae giving negative result.
Hi,
I would like the result to be zero. Thanks Brian "Toppers" wrote: what do you want the result to be if C2 < 10? "BrianC500" wrote: Hi, I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
cell formulae giving negative result.
I think the formula you want is -
=IF(C2=49.99,39.99*0.06,IF((C2-10)*0.06<0,0,C2-10)*0.06) Regards. Bill Ridgeway Computer Solutions "BrianC500" wrote in message ... Hi Bill, Thanks for the reply. I would like the fomula to return a figure of zero. Regards Brian "Bill Ridgeway" wrote: Firstly there is an error in the formula - IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) IF(C2=49.99,39.99*0.06,IF(C2<49.99,(C2-10)*0.06)) Now, to answer the question. You can't stop the formula returning a negative number because it is a mathematical function. You can, however, change what it does when it gets a negative number. What do you want to happen when the formula returns a negative number? Regards. Bill Ridgeway Computer Solutions "BrianC500" wrote in message ... Hi, I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
cell formulae giving negative result.
Hi Bill,
Thank you very much for all your help. That calculation was perfect. I can press on with my spreadsheet now. Regards Brian "Bill Ridgeway" wrote: I think the formula you want is - =IF(C2=49.99,39.99*0.06,IF((C2-10)*0.06<0,0,C2-10)*0.06) Regards. Bill Ridgeway Computer Solutions "BrianC500" wrote in message ... Hi Bill, Thanks for the reply. I would like the fomula to return a figure of zero. Regards Brian "Bill Ridgeway" wrote: Firstly there is an error in the formula - IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) IF(C2=49.99,39.99*0.06,IF(C2<49.99,(C2-10)*0.06)) Now, to answer the question. You can't stop the formula returning a negative number because it is a mathematical function. You can, however, change what it does when it gets a negative number. What do you want to happen when the formula returns a negative number? Regards. Bill Ridgeway Computer Solutions "BrianC500" wrote in message ... Hi, I'm using the calculation IF(C2=49.99,39.99*0.06,IF(C2<49.99(C2-10)*0.06)) How can I stop achieving a negative result when I put a figure of less than 10 in C2? Any help very much appreciated Brian |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
| VBA | Excel Worksheet Functions | |||
| cell calculation check if result is negative and zero out if not l | Excel Worksheet Functions | |||
| How do i get excel to format a cell for a negative result. | Excel Discussion (Misc queries) | |||
| Amount or Numbers in Words | New Users to Excel | |||
Linear Mode
