Counting numbers II

  • Thread starter Thread starter Parbuster18
  • Start date Start date
P

Parbuster18

I am looking for a function to calculate this numbers.

col.A
1 -5
2 -24
3 8
4 12

I am looking for the difference between two number after
telling the cell to pick the largest number.

Max(A1:A4)-A1=7
This is not what I am looking for the number should be 17.

And if you turn it around.
A1-Max(A1:A4)=-17

That is not right either and I need to apply the same
function to all four rows.
A1-Max(A1:A4)=-17 Should be 17
A2-Max(A1:A4)=-36 Should be 36
A3-Max(A1:A4)=-4 Should be 4
A4-Max(A1:A4)=0 Should be 0

And if you had two negitive numbers this would not work
even if you put ABS in front of the function to do away
with the minus sign.

Min(A1:A4)-A1=-19 and I am looking for an answer of 29.

Any Help.
 
Hi again!!

I think the problem is that you are using MAX to calculate an array. That
means that instead of using Enter, to finish the formula, you need to hit
Ctrl-Shift and Enter. If you do this correctly, the formula bar will show
the formula with curly brackets round it {}

Andy.
 
Try to stay in the same thread..

Max(A1:A4)-A1


returns 17 not 7

copied using absolute references (=MAX($A$1:$A$4)-A1) returns this

17
36
4
0
 
Hi Parbuster
if we are only talking integers try the following for calculation the
difference between two numbers:
=ABS(A1-A2)

For your problem use the following:
=ABS(MAX(A1:A4)-A1)

On note to your first example
Max(A1:A4)-A1 return 17 and not 7 (12-(-5))

HTH
Frank
 
I'm going mad!! It works for me without array-entering it!!

Time for an early night, I reckon.

Andy.
 
Max(A1:A4)-A1=7
This is not what I am looking for the number should be 17.
Click to expand...

The number *is* 17. MAX(A1:A4) = 12.

A1 = -5. therefore 12 - (-5) = 17.

???,
Andy
 
Back
Top