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.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
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