Please help with formula

[exntluser]

I would like a formula that would give me the following answer:

If
A1 displays 10.5
B2 displays 10.3
B3 displays 10.7
B4 displays 10.1

Then
C2 displays difference between A1 & B2
C3 displays difference between A1 & B3
C4 displays difference between A1 & B4
etc etc

I hope this makes sense.....

Thank You in advance for any replies.

 

[Joe User]

C2 displays difference between A1 & B2
C3 displays difference between A1 & B3
C4 displays difference between A1 & B4
etc etc

Ostensibly, put the following formula into C2 and copy down through C4 and
beyond:

=$A$1 - B2

However, you did not explain what "difference" you want: A1-B2 or B2-A1.
Make adjustments to the above as needed. If you always want a non-negative
"difference", then use the following formula:

=ABS($A$1 - B2)

Finally, you note that A1 "displays" 10.5 and B2 "displays" 10.3. Perhaps
you are aware that the __actual__ value might be different from the
displayed value.

Just to be sure, if you want the "difference" to be based on the
__displayed__ value, and the displayed value is rounded to 1 decimal place
due to formatting, it would be prudent to use the following formula:

=ROUND($A$1,1) - ROUND(B2,1)

Even better, use ROUND in the formulas for A1, B2 etc, unless they contain
constants.


----- original message -----

 

[PJ]

In C2 enter the formula =$A$1-B2 and drag it down

 

[exntluser]

Many Thanks Joe

Ostensibly, put the following formula into C2 and copy down through C4 and
beyond:

=$A$1 - B2

However, you did not explain what "difference" you want: A1-B2 or B2-A1.
Make adjustments to the above as needed. If you always want a
non-negative "difference", then use the following formula:

=ABS($A$1 - B2)

Finally, you note that A1 "displays" 10.5 and B2 "displays" 10.3. Perhaps
you are aware that the __actual__ value might be different from the
displayed value.

Just to be sure, if you want the "difference" to be based on the
__displayed__ value, and the displayed value is rounded to 1 decimal place
due to formatting, it would be prudent to use the following formula:

=ROUND($A$1,1) - ROUND(B2,1)

Even better, use ROUND in the formulas for A1, B2 etc, unless they contain
constants.


----- original message -----