Hi, I'm not great at Excel and I need a way to use negative numbers in calculations as zero.
Here's my whole formula:
If the value for Calculator!$E10 is a negative number I need it to calculate as 0.
Please Login or Register to view this content.
My gut wants to do something like this:
But the negatives are reporting out as positive (ie, 1 - -1 = 2) when the answer should be 1.Please Login or Register to view this content.
Help?
Last edited by kuzniak; 12-18-2015 at 04:41 PM.
The only place I see E11 in your post is in the line I quoted.If the value for Calculator!$E11 is a negative number I need it to calculate as 0.
I don't see it in your formulas.
Sorry E10. I edited my post to reflect.
Sorry, E10. I edited my post to reflect.
If you have a subtraction which can be negative you can replace:
Calculator!F10-Calculator!$E10 with: (MAX(0,Calculator!F10-Calculator!$E10))
Last edited by protonLeah; 12-18-2015 at 05:07 PM.
Ben Van Johnson
How about substituting this into your formula.
Formula:
Please Login or Register to view this content.
Ah, when I make the substitution the resultant is #NAME?
???
Can you post your formula as you have it entered (copy and paste here)?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Can you show how you substituted?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
If I make the substitution I get #NAME?Originally Posted by protonLeah
If the O is supposed to be a 0 then it calculates like this: 1- -1=2 when I want this: 1- -1=1
This is what I have:
=IF(((Calculator!F10-Calculator!$E10)*Calculator!F$5/Calculator!F$4)>$D5,(Calculator!F10-Calculator!$E10)*Calculator!F$5/Calculator!F$4,"dl")
I need the Calculator!$E10 value to be recognized as 0 if it is a negative number.
=IF(((Calculator!F10-max(0,Calculator!$E10))*Calculator!F$5/Calculator!F$4)>$D5,(Calculator!F10-max(0,Calculator!$E10))*Calculator!F$5/Calculator!F$4,"dl")
Sorry, that was supposed to be zero not O:
if the remainder from the subtraction is negative.Please Login or Register to view this content.
Thank you, however your formula changes the negative into a positive and subtracts such as: 1 - -1=0
I need 1 - -1=1
Wait! I found my mistake!
I have no idea how this works but thank you this did it!
1- -1 = 2
in most states.
Anyways thanks everyone!
Not when -1 represents an illogical number.
Happy to help, and thanks for the feedback
It is based on 0 being > any negative, and any positive being > 0...
Calculator!F10-max(0,Calculator!$E10)
So F10-(whichever is greater, 0 or E120)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks