Formula for determining negative value change between cells

H

Hawkster

Hello Everyone,

I currently have a sheet with 2 columns of data as follows...

Coulmn A Column B
1 0
1 0
1 0
0.5 0
1 0
0 1
0 1
0 1

I would like a formula which would only show negative changes from
column A to B.

So in the above example the result should return "-4.5".

Any help would be appreciated.

Thanks
Neil
 
J

joeu2004

Hawkster said:
I currently have a sheet with 2 columns of data as follows...
Coulmn A Column B
1 0
1 0
1 0
0.5 0
1 0
0 1
0 1
0 1
I would like a formula which would only show negative changes from
column A to B. So in the above example the result should return "-4.5".
Click to expand...

One solution:

=sumproduct((B1:B8<A1:A8)*(B1:B8-A1:A8))

I am not sure why the following array formula (ctl-shift-Enter) does
not seem to work:

=sum(min(0, B1:B8 - A1:A8))

Perhaps someone can comment.
 
R

Ragdyer

Positives offset the negatives.

There's no separation of negatives and positives, just totalization.

Select the "B1:B8 - A1:A8" in the formula bar and hit <F9>.
See what you get.
Then select the "MIN(0, B1:B8 - A1:A8)" and hit <F9>, and see what you get.
That's all that's left for the SUM() to calculate.

Do the same with the Sumproduct() formulas,
OR, with this *array* formula:

=SUM(IF(B1:B8-A1:A8<0,B1:B8-A1:A8))
 
S

Sandy Mann

Ragdyer said:
Positives offset the negatives.

There's no separation of negatives and positives, just totalization.
Click to expand...

Perhaps I am misunderstanding you RD. If we have
{-1;-1;-1;-0.5;-1;1;1;1}retuned by the B1:B8 - A1:A8 in the array formula
then MIN(0, {-1;-1;-1;-0.5;-1;1;1;1}) even in an array formula will *not*
return:

{(0,-1),(0,-1),(0,-1),0,-0.5),(0,-1),(0,1),(0,1),(0,1)}
resolving to {-1,-1,-1,-0,5)0,0,0} as I believe joeu2004 was thinking that
it would

I may be wrong, (again!) but I have always assumed that presenting MIN with
(0, {-1;-1;-1;-0.5;-1;1;1;1}) is the same as presenting it with
(0,-1;-1;-1;-0.5;-1;1;1;1) and MIN(0,-1;-1;-1;-0.5;-1;1;1;1) is -1. In
other words MIN will not work in array formulas

My apologies if I misunderstood what you are saying.

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
J

joeu2004

Sandy said:
If we have
{-1;-1;-1;-0.5;-1;1;1;1}retuned by the B1:B8 - A1:A8 in the array formula
then MIN(0, {-1;-1;-1;-0.5;-1;1;1;1}) even in an array formula will *not*
return:
{(0,-1),(0,-1),(0,-1),0,-0.5),(0,-1),(0,1),(0,1),(0,1)}
resolving to {-1,-1,-1,-0,5)0,0,0} as I believe joeu2004 was thinking that
it would
Click to expand...

Thanks for a __reasonable__ attempt to explain my mistaken thinking.
What you say makes good sense.

The following models exactly what I had intended. {sum(log(A1:A5,10))}
seems to be interpreted as sum(log(A1,10),log(A2,10),...). So if A1
has 10, A2 has 100, etc up to 100000 in A5, {sum(log(A1:A5,10))} yields
15; that is, 1+2+...+5.

And so I thought that {sum(min(0,B1:B8-A1:A8))} would be interpreted as
sum(min(0,B1-A1), min(0,B2-A2),...).

I had not stopped to consider the fact that since MIN() takes a list of
parameters, min(0,B1:B8-A1:A8) is ambiguous, and it is quite reasonable
that Excel interprets it as min(0,B1-A1,B2-A2,...). My bad!
 
R

Ragdyer

I don't pretend to be any kind of XL computational expert.
All I know, is what I see occurring under differing circumstances.

The use of <F9> on selected areas of formulas has taught me a lot.
Maybe not *why*, but definitely what *does* happen.

The why I leave to the Harlans, Danas, Aladins and Jerry Lewis'.
 
S

Sandy Mann

Hi Joeu2004,

The only reason that I *knew* that MIN did not work the way that you were
trying to use it was that I have tried to use it exactly the same way as you
were trying in the past.

I had better correct a statement that I made in my previous post:
(0,-1;-1;-1;-0.5;-1;1;1;1) and MIN(0,-1;-1;-1;-0.5;-1;1;1;1) is -1. In
other words MIN will not work in array formulas
Click to expand...

I should have said that MIN does not work *like that* in array formulas.
MIN(0,A1:A60-B1:B60) entered as an array formula does seem to work but MIN
will only return ONE value - the lowest value of {0, A1-B1, A2-B2........}

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
Top