IF Function Common To Work with multiple combination of two negative & positive numbers

[RajNY]

I have given in the attached file six situations where I need help to get the results shown as "expected results" using one common IF Function. Pls Help. Thanks, Raj

					Situation 1		Situation 2		Situation 3		Situation 4		Situation 5		Situation 6
	Number 1		A		1,500		700		(75)		(500)		350		(180)
	Number 2		B		800		900		(300)		(200)		(475)		630
	A Minus B				700.00		(200.00)		(375.00)		(700.00)		(125.00)		(810.00)
	Expected Result		700.00		(200.00)		225.00		(300.00)		825.00		450.00
	Need to use the same common IF Function for all SIX situations
					If Function Correct		If Function Correct		If Function Wrong		If Function Wrong		If Function Wrong	If Function Wrong
	Manual Calc								225​		-300​
	Formula				700		(200)		225		(300)		825		(810)
					700		(200)		225		(300)		825		(810)

 

[Micron]

Numbers in parentheses are negative numbers. Therefore negative 75 (-75) minus negative 300 (-300) or (75)-(300) is 225 so the answer you get is correct. Same for the other answers, so not sure if you want something else or you're just mistaken about the results you should be getting.

 

[RajNY]

Numbers in parentheses are negative numbers. Therefore negative 75 (-75) minus negative 300 (-300) or (75)-(300) is 225 so the answer you get is correct. Same for the other answers, so not sure if you want something else or you're just mistaken about the results you should be getting.

Hello Micron. Thanks for your reply and guidance tips. I realize that I need to show exactly what is happening in my excel file but am not able to attach the excel file. I then saw the tip to "add-in" xl2bb which I tried several times to add to my excel but I am repeatedly getting a message that "This file type is not supported in Protected View". So my first task is to have this add-in to my excel so that my question can be clearly seen in the excel file.

If you can give some tips for this add-in, that would be great. Thanks.

 

[Micron]

I don't use it. I spend most of my time on here helping, not seeking help so that's why. I don't think you need to worry too much about 'what's happening' until it's clear that you understand a basic thing about mathematics. Subtracting a negative number from any other number is easier to understand if don't think of it as "minus a negative number" but instead think of it as "adding a positive". So in your example
negative 500 minus negative 200
is the same as
negative 500 plus positive 200
which equals -300.

What you claim to be the wrong result

is in fact the correct result. You have to get your mind around that first.

 

[RajNY]

I don't use it. I spend most of my time on here helping, not seeking help so that's why. I don't think you need to worry too much about 'what's happening' until it's clear that you understand a basic thing about mathematics. Subtracting a negative number from any other number is easier to understand if don't think of it as "minus a negative number" but instead think of it as "adding a positive". So in your example
negative 500 minus negative 200
is the same as
negative 500 plus positive 200
which equals -300.

View attachment 105268

What you claim to be the wrong result

is in fact the correct result. You have to get your mind around that first.

Once again Thanks Micron. Let me my try out what you are suggesting. I am clear about the example of two negative numbers you gave but what I am trying to achieve is one common IF Function to capture all my six (I think 6 situations is most I can envisage) situations without looking at which number of the two is higher / lower / positive / negative etc. But before I take up any more of your time, let me first try what you are suggesting and I will post my experience after that.

In the meantime,

This is the IF Function I am using for all six situations:

=IF(F6<0, F5+F6, F5-F6) where F5 is A series numbers and F6 is B series numbers. I even tried to see but failed to see if I can use IF Function with 2 IFs.

Another row of answers is using: =F5-F6. (shown in Manual Calc Formula Row in my screen shot)

 

[Micron]

No one can tell which column is F by looking at your first post. I suggest that you use XL2BB utility to post data and formulas from your sheet. But really I do not see what there is to test. I do not see why you need an If formula at all. See situation 3: (75) - (300). If you just subtract B from A, the answer is 225 regardless of what you show as "expected". Your "expected" is incorrect. Usually someone either wants the simple and true result (and that is 225 in this case) or they want the absolute (e.g. 225, not negative 225). As long as both values are numbers, you should not have an issue with a simple A minus B formula. If the result must be positive BUT shows the correct digits then you can use ABS function to return +225 instead of -225