Formula for Value Return Based on Positive and Negative Values

[David Roberts]

I'm trying to work out a formula that give me the correct return in 'E' based on the difference between 'C' and 'D'

I'm trying =SUM(ABS(C2:D2)) This seems to work for answers £51.00 (E2) and £50.00 (E3), but I can't get E4 to equal £49.00

Regards
Dave

 

[Joe4]

Welcome to the Board!

If the values currently shown in column D are what you want to see, there is no need to use the ABS function. Just use the simple formula:

=D2-C2

If that does not give you what you want, please explain in more detail what the correct results need to look like.

 

[David Roberts]

Hi Joe

I can't believe it was that easy! I've done nothing more than over complicate the return.
I'm off to sit in a darkroom with a bucket load of coffee.
Many thanks

 

[Joe4]

You are welcome.

 

[David Roberts]

Just when I thought my issue was straight forward the problem has bit me!

The issue I have come across now is the following:

The difference between the Customer Price and Sage price gives the correct return in 'C'. The transport cost is then added thus giving the return in 'E' in this case £33.52 which is correct.

However

The difference between Customer Price and Sage Price (A3-B3) gives C3 which is correct. By then including the transport cost of £50 (D3) then E3 needs to return an answer of Zero.

So, where the Customer Price is greater than the Sage Price the difference is added to the transport cost thus giving a low amount.

Where the Sage Price is Greater than the Customer Price, then the difference should be subtracted from the transport cost. So the example above in E3 should be £0

How do I formulate to include all possibilities?

 

[Joe4]

Try this formula in E2:

=IF(A2>B2,D2+C2,D2-C2)

 

[David Roberts]

Thanks for coming back to me.
What happens if A2<B2? How do you combine the option of A2>B2 and also A2<B2 into one formula?

 

[Joe4]

Thanks for coming back to me.
What happens if A2<B2? How do you combine the option of A2>B2 and also A2<B2 into one formula?

It is already accounted for. It is already "baked into the formula".
The key is understanding how the IF formula works.
The structure of it is this:
=IF(condition,what to do if condition is TRUE,what to do if condition is FALSE)

So here are the three arguments:
condition: A2>B2
what to do if condition is TRUE: D2+C2
what to do if condition is FALSE: D2-C2

If the condition is false, then really what that is checking is A2<=B2
And in that case, it will do D2-C2

So, essentially what that formula is doing is:
If A2>B2, then do D2+C2
If A2<=B2, then do D2-C2
which, from my understanding of your question, is what you are looking for.

Try it out and see for yourself!

 

[David Roberts]

Joe

Many thanks for the explanation, I fully understand the workings now.

Regards
Dave

 

[Joe4]

You are welcome.
Glad I was able to help!