In the false part of the If statement below (F3-E3)/E3), I would like the result to display as a negative number regardless if the result is a positive number. Can someone help me do this?
=IF(F3-E3/E3<"1",ABS(F3-E3)/E3,(F3-E3)/E3)
In the false part of the If statement below (F3-E3)/E3), I would like the result to display as a negative number regardless if the result is a positive number. Can someone help me do this?
=IF(F3-E3/E3<"1",ABS(F3-E3)/E3,(F3-E3)/E3)
Last edited by rhudgins; 03-04-2011 at 03:31 PM.
=if(f3-e3/e3<"1",abs(f3-e3)/e3,((f3-e3)/e3)*-1)
How about
= -ABS((F3-E3)/E3)
Does that work for you?
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
Neither of those solutions work. They both display positive percentages. The formula is in H3
First: You shouldn't have <"1" it should be <1
"1" is text
Second: You are missing the brackets for your test: F3-E3/E3 instead of (F3-E3)/E3
Third: You missed the minus sign and brackets from ChemistB's suggestion
Your formula should be: =IF((F3-E3)/E3<1,-ABS((F3-E3)/E3),(F3-E3)/E3)
Actually, with my formula, you don't need the IF statement at all
= - ABS((F3-E3)/E3)
Are you sure? OP is testing for less than 1, not less than 0.
I tried a few examples and seems to be consistant. Can you give an example where it doesn't work? I may be wrong. It happens.
Thanks for all of your help!!
ChemistB
I guess it's moot now (because it's SOLVED) but with your formula you always get a negative result. The IF() version gives a positive result when F3>=E3. I interpreted OP's request to account for that.
You're right Cutter. I was looking at it wrong. :-/ Nice call.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks