formula help

[jim may]

In Range A1:E2 I have values

1.89 1.99 2.09 3.40 2.25
1.90 2.10 1.90

I need in Cell F2 a formula to produce the figure -0.33 (1.90-1.89+2.1-2.09+1.90-2.25)
The 2nd row minus the first row, but only include or consider the columns that have a value
on the 2nd row

Any thoughts?

TIA,

 

[jim may]

try layout again
1.89 1.99 2.09 3.40 2.25
1.90 0 2.10 0 1.90

 

[Hanz77]

If you aren't looking for any too complex, the following should suffice.

=(IF(A2<>"",A2-A1,0))+(IF(B2<>"",B2-B1,0))+(IF(C2<>"",C2-C1,0))+(IF(D2<>"",D2-D1,0))+(IF(E2<>"",E2-E1,0))

 

[SydneyGeek]

=SUM(IF(NOT(ISBLANK(A2:E2)),A2:E2-A1:E1,0))
Confirm with Ctrl+Shift+Enter

Denis

 

[Hanz77]

just noticed you changed blank to zero. Following is an update:

=(IF(A2<>0,A2-A1,0))+(IF(B2<>0,B2-B1,0))+(IF(C2<>0,C2-C1,0))+(IF(D2<>0,D2-D1,0))+(IF(E2<>0,E2-E1,0))

 

[jim may]

Thanks

My example was too simple - I need 8 comparisons, so that means the normal If stat would not work as I would have broken the the 7 If Limit. The use of the "+" allows
unlimited Ifs - Thanks I got it!!

 

[pgc01]

Hi Jim

If the values in the cells are numbers, here is another option:

=SUMPRODUCT((A2:E2>0)*A1:E2*{-1;1})

 

[jim may]

Thanks PGC - This works great
I've got another level of complexity (just discovered).
I can have in my column(s) Match - any one of 8 columns (all numbers)
1.89
2.05

or
1.89
{blank}

and now
{blank}
1.90

So I need to consider ONLY the 2.05 scenarios Only in my Outcome..

Any additional thoughts?
Thanks,
Jim

 

[pgc01]

So, if I understand correctly, you only want the cases when both values in the column are bigger than 0.

Try:

=SUMPRODUCT((A1:E1*A2:E2>0)*A1:E2*{-1;1})

 

[pgc01]

Or,maybe easier, similar to Denis solution:

=SUMPRODUCT((A1:E1*A2:E2>0)*(A2:E2-A1:E1))