Multiple Embedded IF/AND Statements

[ngann]

This below formula is only returning the first IF Statements Value if True value and everything else is just 0. Does anyone know why this is happening?

=IF((AND(B3<G3>0,B3<L3>0,B3<Q3>0,B3<V3>0)),B3,IF((AND(G3<B3>0,G3<L3>0,G3<Q3>0,G3<V3>0)),G3,IF((AND(L3<B3>0,L3<G3>0,L3<Q3>0,L3<V3>0)),L3,IF((AND(Q3<B3>0,Q3<G3>0,Q3<L3>0,Q3<V3>0)),Q3,IF((AND(V3<B3>0,V3<G3>0,V3<L3>0,V3<Q3>0)),V3,"")))))

 

[JustynaMK]

Hi ngann,

This is because statements like B3<G3>0 are partially invalid and will always evaluate only the first part (B3<G3). Try to paste =B3<G3>0 into a separate cell and see how it behaves depending on B3 and G3 values. You will notice that "G3>0" part is always being ignored.

In order to solve it you'd need to adjust all such statements in your formula to evaluate both parts, i.e. =AND(B3<G3,G3>0)

Hope it helps.

 

[Special-K99]

Can't you replace all that with

=MIN(B3,L3,Q3,V3)

 

[JustynaMK]

+ edit - sorry, all my formulas were "cut" when I posted my reply (HTML issue) and only just now I realised how silly it looks...

Just to rephrase (with spaces):

This is because statements like "<g3 style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">B3 < G3 > 0" are partially invalid and will always evaluate only the first part. Paste "=<g3). try="" to="" paste="B3<G3">B3 < G3 > 0" into a separate cell and see how it behaves depending on B3 and G3 values. You will notice that "G3>0" part is always being ignored.
In order to solve it you'd need to adjust all such statements in your formula to evaluate both parts, i.e. =AND(B3 < G3, G3 > <g3,g3>0)

+ But it looks like Special-K99's solution is much better.</g3,g3></g3).></g3>

 

[ngann]

I cant use the MIN formula because I need it to return the lowest value that is >0. If I use the MIN formula it will only return 0.

 

[Special-K99]

=MIN(IF(b3>0,b3,9.9E+307),IF(l3>0,l3,9.9E+307),IF(q3>0,q3,9.9E+307),IF(v3>0,v3,9.9E+307))