Difference formula

[BrettRust]

I require a formula that recognises the difference between two numbers.
Eg in cell a1 we have 1.5. in cell a2 we have 2.7. in cell a3 i want the value 1.2
displayed.
I require the difference formula as the value in cell a2 will be greater in some instances than the value in cell a1 so a simple subtraction sum is not sufficient.

Thanks in advance for your assistance

 

[xladept]

Hi BrettRust,

If you want a formula in the worksheet just use:

=ABS(A1-A2)

in A3

 

[cyrilbrd]

Hi,

=IF(B2>A2,B2-A2,A2-B2)

?

 

[BrettRust]

Many thanks for your help.
I can work with those, another question that goes a little further than the last one:
Eg in cell a1 we have 7. in cell a2 we have 4. in cell a3 i want to calculate 2/3 or 67% of the difference between a1(7) and a2(4). But pointed towards cell a2 value.
So in cell a3 i want the result to be 5.
Another example: al=4. a2=6. difference of 2. 67% of this 1.33. a3 result=5.33.
I will have other examples that are calculated at 50% and 33% of the difference so a formula that i can just swap the percentages over would be great.

I have tried taking the calculated difference and then adding to the cell a2 only problem is that you might see is that in first example i would have to add to subtract from cell a1 (calc diff =2, subtract from cell a1=5) then for 2nd example i need to add to cell a1 (calc diff=1.33, add to cell a1=5.33).

This is a problem as values in a1 could be higher than a2 and vice versa likie given examples. I want to cut as much human calculation/work out of this sheet as possible. Hope you follow!
Cheers
Brett

 

[Diffy]

You can use =Abs(a1-a2)/max(a1,a2)

The max function will return the bigger of a1 or a2.

-Diffy

 

[xladept]

Hi BrettRust,

This works on your examples:

=2*(A2-A1)/3+A1

the formula in A3.

 

[jasonb75]

Just to point out, there is a difference between 67% and 2/3, not much of a difference, but enough to return discrepancies.

For a percentage formula

=A1+((A2-A1)*67%)

For a fractional formula, use XLAdept's suggestion, the quoted version below illustrates how 2/3 is incorporated to make editing easier.

=2*(A2-A1)/3+A1

 

[BrettRust]

This is part of my scoring projection for MLB baseball games. Cell A2 is the starters pitching average. A1 is the opponents projected score. For me this is the last part of my spreadsheet. Depending on the amount of games played by the pitcher i assign a weighting of between 33-67% of the difference eg. Unproven pitcher low, regular pitcher high pct.

Getting this bit right is critical as previous 2 years its all been manual entry!, and now im starting to understand excel better i am trying to get the whole sheet formulated.

Really appreciate your help here, its quite amazing the things excel can do. Il let you know how i get on.

 

[BrettRust]

Well pleased with that, I have gone for the percentage option as i might adjust the percentages away from the more rounded 33,50,67.
I thought for a minute i was asking alot. Or people would be completely confused by the request.
How do you get to that level?? Is it just practice and exprerimenting?
Cheers
B

 

[cyrilbrd]

if you wish to adjust the %, use jasonb75's formula but set a reference instead of the 67%:

=A1+((A2-A1)*B1)

With 67% in B1, you could then modify the percentage in B1 to fit our needs.
You could also use a data validation to select from 33,50,67.

2	67%		3.34
4