Problem using Worksheet functions in VBA (FLOOR, SMALL & COUNTIF)

[martinbanks]

Can anyone see what is wrong with the following formula? I am trying to set my minimum value of my chart axis to be the next multiple of 50 down from the smallest value in the ranges shown, but they contain zero values so it is attempting to overcome those with the SMALL/COUNTIF/LEN shenanigans

Please Login or Register  to view this content.

[martinbanks]

BTW the reason I am not just using MIN is because one of the ranges has a formula in it so it seems to throw it off

[martinbanks]

BTW the reason I am not just using MIN is because one of the ranges has a formula in it so it seems to throw it off

(Did not mean to post twice, and cannot find a way to delete it, my apologies)

[shg]

Maybe ...

Please Login or Register  to view this content.

[martinbanks]

That looks great. I have never heard of or used Evaluate before but if it works the way it looks like then it will be a great new weapon to my arsenal

One question; will it work like this:

Sheet1.ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = Evaluate("floor(min(if(B3:B45>0, B3:B45), if(E3:E45>0, E3:E45)), 50)")

[martinbanks]

YOU BEAUTY!!!! That works wonderfully Thank you very much. You just gave me a very flexible, crafty weapon. Much appreciated my friend