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

 

[Snakehips]

Andy, is the result of your formula number in form of text?

 

[Snakehips]

If your formula is = xyz maybe test..... = 1*(xyz)

 

[andymalan]

If your formula is = xyz maybe test..... = 1*(xyz)

Hi Snakehips, thank you for picking my question up.
Just for my own peace-of-mind...........did you see the screen shots I sent with my question?

 

[davesexcel]

Snakeshiips is asking if the formula in AC2 result is text, if it is, then multilpy the formula by 1 to turn it into a number

 

[Snakehips]

Yes, a bit blurry but saw screenshots.
If you post regularly then it's definitely worth downloading and using XL2BB, in order to post a meaningful snippet of your spreadsheet.

As @davesexcel says, I'm asking if the result of your formula eg '1' is text? If it is then it will be ignored by your MAX formula and explain the 0 result.
Prove it ,or not, by editing the formula as suggested.

 

[andymalan]

the result of the formula in AC2 is NOT in text form and that is why the formula in Y2 result is 0.
Formulas
AC2 =IF($U2=$AC$1,$T2,""). the data in cells U2 and AC1 match, therefore the content of T2 is returned, "1"
Y2 =MAX(AC2,AE2,AG2,AI2,AK2,AM2,AO2,AQ2). the formula in Y2 should result in a "1", as AC2 is part of the range that "max" is processing, but it remains "0".

if I type a "1" into AC2, Y2 results in "1".

What can I do to have Y2 return a "1", without losing the formula in AC2?

thanking you in anticipation,
Andy

 

[Fluff]

Is T2 a formula or hard value?
If it's a formula what is it?

 

[andymalan]

Book1
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	EdgeThickness	edging_description	Actual Height	Actual Width	max	max	No Edging	No Edging	E 1 L	E 1 L	E 1 L 1 S	E 1 L 1 S	E 1 L 2 S	E 1 L 2 S	E 1 S
2	1	E 1 L	720	559	0	0			0	1
3
Sheet3

 

[Snakehips]

Andy, Your formula in T is returning TEXT ... "1"
Try as I said and mod one or other formula to convert to NUMBER