Return any value greater than 0 from a range, using excel formula or vba.

[andymalan]

Greetings, esteemed volunteers.
In cell Y2, I am trying to get any number from the range AC2, AE2, AG2, AI2.
Y2 has the formula: =MAX(AC2,AE2,AG2,AI2), but even though the cell AC2 displays a value, Y2 returns a 0.

Each of the cells (AC2,AE2,AG2,AI2) has a formula to determine what value is displayed, as can be seen in AC2 which returns a value of 1.
If the formula in AC2 is deleted and a "1" is typed into the cell, then Y2 does indeed return the value of 1.

your help is always appreciated.
kind regards
Andy

 

[Tom.Jones]

Try in AC2
=IF($U2=$AC$1,$T2+0,"")
or in T2
=IF(Sheet1!W2>0,--LEFT(S2,(FIND("mm",S2,1)-1)),"")

 

[andymalan]

Thanks everyone, especially Snakehips, i so appreciate your time and expertise.

 

[Snakehips]

You're welcome, and well done for using XL2BB