Non-Standard Mathematics (Negative Numbers)

[RRavens]

Hi everyone,

I've stumbled onto a little bit of a conundrum trying to get Excel to utilise a function which does not adhere to standard mathematics, resulting in a ‘wrong’ value being returned (correct in standard terms, incorrect with how I need it to work.)

Before I detail what I need to happen, I know that this goes against everything we are taught in school - But for the spreadsheet I am creating I require a column to calculate a value based on this 'wrong mathematics'.

What I need to happen is when a negative number is subtracted from another negative number the result (I know wrongly) needs to be the value of their difference which will either be negative if the first value taken is larger than the second, and positive if the first value is lower than the second.

For example:

	A	B	C
1	-10	-2	-8
2	-2	-10	8

If the value in A1 is -10, and the value in B1 is -2 I need the result in C1 to be -8.
If the value in A2 is -2, and the value in B2 is -10 I need the result in C2 to be 8.

In the grander scheme, here is a row from the spreadsheet which gives a perfect mathematical answer, but as I have explained needs to return a 'non-standard' answer.

	A	B	C	D	E	F	G	H	I	J	K	L
24	15	6	12	21	-9	6	12	9	3	-19	5	9

The formula for J24 is:

Please Login or Register  to view this content.

As A24 is not less than or equal to L24, we use the False function:

(5-6)-9*2
(-1)-9*2
(-1)-18
= -19

The value in J24 is mathematically correct, but I need it to return a value of -17

(5-6)-9*2
(-1)-9*2
(-1)-18

The difference of 1 and 18 is 17.
The resultant value of L24*2 is the 'first' number despite the order in the formula as it needs to work for other values outside these negative-negative exceptions.
-18 > -1 therefore the result is a negative number

= -17

I have tried composing a formula for this function, but with no luck as I can not change the values which are used in the calculation - If they are a negative they must be displayed as such, so using the ABS function isn't possible. This is why I have started thinking of using a user-defined function for the calculation, but I'm not sure where to start. Furthermore it must utilise standard mathematics for any other calculation outside the negative-negative scenario, so -10 + 5 will still be -5; and it is this mix of styles which is making it hard for me.

I know this is incredibly confusing, but it is what I need the spreadsheet to do.

Any help would be greatly appreciated.

P.S. Failing a method to circumvent normal mathematics, it would be acceptable if I could use Conditional Formatting to highlight any cell in the column (Column J in the above example) which has been calculated between two negative numbers as this will allow the user to perform a manual calculation on these values.

[FDibbins]

Hi and welcome to the forum

yes that is a bit confusing, perhaps you could upload a sample workbook with some examples of your expected outcome?

Also, have you tried playing around with the =sign() function?

[mehmetcik]

Hi

This is easily done as a user defined function.

is it only two values at a time?

I'll create one for you.

[shg]

Looks pretty standard to me:

Please Login or Register  to view this content.

[mehmetcik]

Hi

It seems that if you have -a + -b all you need is to change the sign on the minus b.

-10 + - 2 => -10 + + 2 = -8
-2 + - 2 => -2 + + 10 = 8

so you could use a formula:

Please Login or Register  to view this content.

There is a user defined function on the attached sheet that for you.

Please Login or Register  to view this content.

Enjoy

[shg]

Your user-defined function should be called 'Subtract'

[RRavens]

Thank you for the replies, and thank you very much to mehmetcik (+rep) whose reply has solved my problem.

Looking at the answer, I'm surprised I entirely missed it - But I am very grateful to finally be able to put this problem aside and complete the spreadsheet.