Two columns, locate the minus figure and calculate

[AugustusHaynes]

Hello,


Hopefully I'm explaining this well.


So I've got 2 columns, or 2 cells as the example. One would have -1.5 and the other 1.5, I need to use a formula that will identify which one is the minus figure (could be in either column/cell) and then compare that minus figure to another cell. So in my example, it would identify -1.5 as the minus and then compare it to say -.0.8 and produce a result of -0.7. Ideally it would work both ways though, so if the other cell was +2.1 it would produce a result of 3.6 as the difference.




Thanks very much.

 

[Fluff]

How about

Excel 2013 32 bit
	A	B	C	D
2	1.5	-1.5	-0.8	0.7
3	1.5	-1.5	2.1	3.6
Sheet1

Cell Formulas
Range	Formula
D2	=IF(A2A2),IF(B2))

 

[Special-K99]

Does this work?
Produces same results as Fluffs output

=ABS(MIN(A2,B2)-C2)

 

[AugustusHaynes]

Both work perfectly, thanks so much.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[AugustusHaynes]

let me know if I should start a new thread. But this is the same sheet.

The final part of the sheet needs a total for a column where it only counts figures that are either over 2.5 or under -2.5

=COUNTIFS(W2:W17,2.5<W2:W17>-2.5)

Comes back as 0 no matter which why I have the signs or where I play the minus figure. Can't seem to get a sumif & sumif to work but assume that's what sumifs is for.

Thanks in advance.

 

[jtakw]

Hi,

Try this:

Book1
	W	X	Y
2	2		4
3	3
4	4
5	1
6	-1
7	-2
8	-3
9	-4
Sheet239

Cell Formulas
Range	Formula
Y2	=SUM(COUNTIF(W2:W17,{">2.5","<-2.5"}))

 

[AugustusHaynes]

brilliant, works perfectly. Thank you.

 

[jtakw]

You're welcome.