Compare Dynamic cells in a row for Max Value, and return text if any cell entry is a text else return Max value.

[Anly Henry]

I am really new to Excel formulas; I am trying to compare dynamic cells in a row for Maximum Value however if any of the cells which I am comparing has a text they return that text else return the maximum value.
Here is what I applied initially when I started =IF(AND(ISNUMBER(G4), ISNUMBER(I4)), MAX(G4, I4), IF(ISNUMBER(G4), I4, G4))… as you can see instead of considering an entire continuous range from G4:I4 I need to compare G4, I4, L4, N4, Q4, S4 and so on and get the result and if any of the above-mentioned cells has a text value let's say "UR" then return UR as final result else the maximum value of the above-mentioned cells.

 

[Skybluekid]

Try the below

=IF(OR(NOT(ISNUMBER(G4),NOT(ISNUMBER(I4))=TRUE,G4,MAX(G4,I4))

 

[Anly Henry]

Try the below

=IF(OR(NOT(ISNUMBER(G4),NOT(ISNUMBER(I4))=TRUE,G4,MAX(G4,I4))

@Skybluekid Thank you so much for the suggestion, however, I get the below notification (Screenshot attached). Also, my major concern is that I have to compare cells G4, I4, L4, N4, Q4, S4 and return the value in cell E4.

 

[Skybluekid]

Try the below

Book1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
13
14
15		UR		UR		23			UR		UR			UR		UR
16
17
Sheet6

Cell Formulas
Range	Formula
E15	E15	=IF(OR(ISTEXT(G15),ISTEXT(I15),ISTEXT(L15),ISTEXT(N15),ISTEXT(Q15),ISTEXT(S15))=TRUE,"UR",MAX(G15,I15,L15,N15,Q15,S15))

This is with numbers

Book1.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
14
15	25		12		23			4		8			2		25
16
Sheet6

Cell Formulas
Range	Formula
E15	E15	=IF(OR(ISTEXT(G15),ISTEXT(I15),ISTEXT(L15),ISTEXT(N15),ISTEXT(Q15),ISTEXT(S15))=TRUE,"UR",MAX(G15,I15,L15,N15,Q15,S15))

 

[Anly Henry]

Try the below

Book1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
13
14
15		UR		UR		23			UR		UR			UR		UR
16
17
Sheet6

Cell Formulas
Range	Formula
E15	E15	=IF(OR(ISTEXT(G15),ISTEXT(I15),ISTEXT(L15),ISTEXT(N15),ISTEXT(Q15),ISTEXT(S15))=TRUE,"UR",MAX(G15,I15,L15,N15,Q15,S15))

This is with numbers

Book1.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
14
15	25		12		23			4		8			2		25
16
Sheet6

Cell Formulas
Range	Formula
E15	E15	=IF(OR(ISTEXT(G15),ISTEXT(I15),ISTEXT(L15),ISTEXT(N15),ISTEXT(Q15),ISTEXT(S15))=TRUE,"UR",MAX(G15,I15,L15,N15,Q15,S15))

@Skybluekid Thank you so much. It was really helpful at this moment.

 

[Skybluekid]

You are very welcome

 

[Anly Henry]

You are very welcome

@Skybluekid Hope you are well!!!... I just encountered something with the previous scenario that we discussed earlier. I will try my best to explain it in a way that is easy for you to get my concern. Please refer to the below image. I applied the formula in cell E4 and it worked for the first occurrence i.e. Rev AA. I will explain how I want my formula to work.. When I give an entry in H4 & J4 I want excel to compare these 2 cells and return the Max value but if one cell is text then return text, however when I start to give an entry in the second occurrence Rev AB I want Excel to consider M4 & O4 ignoring the entries in H4 & J4 (first occurrence), and when there is third occurrence Rev AC I want excel to consider R4 & t4 ignoring second occurrence and the same applies for fourth occurrence Rev AD. As conclusion, I want the last occurrence "Status" comparison to be the result of "Final Status-E4". Hope I was able to convey my concern, I really hope you can help me with this.

 

[Skybluekid]

Try the below:

Book1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
11
12		Final Status	REV-AA					REV-AB					REV-AC					REV-AD
13			Submission	P1.Date	Status	P2.Date	Status	Submission	P1.Date	Status	P2.Date	Status	Submission	P1.Date	Status	P2.Date	Status	Submission	P1.Date	Status	P2.Date	Status
14		3			3		1			1		UR								3		1
15
Sheet6

Cell Formulas
Range	Formula
E14	E14	=IF(NOT(AND(W14="",Y14="")),IF(OR(ISTEXT(W14),ISTEXT(Y14)),"UR",MAX(W14,Y14)),IF(NOT(AND(R14="",T14="")),IF(OR(ISTEXT(R14),ISTEXT(T14)),"UR",MAX(R14,T14)),IF(NOT(AND(M14="",O14="")),IF(OR(ISTEXT(M14),ISTEXT(O14)),"UR",MAX(M14,O14)),IF(NOT(AND(H14="",J14="")),IF(OR(ISTEXT(H14),ISTEXT(J14)),"UR",MAX(H14,J14)),))))

 

[Anly Henry]

Try the below:

Book1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
11
12		Final Status	REV-AA					REV-AB					REV-AC					REV-AD
13			Submission	P1.Date	Status	P2.Date	Status	Submission	P1.Date	Status	P2.Date	Status	Submission	P1.Date	Status	P2.Date	Status	Submission	P1.Date	Status	P2.Date	Status
14		3			3		1			1		UR								3		1
15
Sheet6

Cell Formulas
Range	Formula
E14	E14	=IF(NOT(AND(W14="",Y14="")),IF(OR(ISTEXT(W14),ISTEXT(Y14)),"UR",MAX(W14,Y14)),IF(NOT(AND(R14="",T14="")),IF(OR(ISTEXT(R14),ISTEXT(T14)),"UR",MAX(R14,T14)),IF(NOT(AND(M14="",O14="")),IF(OR(ISTEXT(M14),ISTEXT(O14)),"UR",MAX(M14,O14)),IF(NOT(AND(H14="",J14="")),IF(OR(ISTEXT(H14),ISTEXT(J14)),"UR",MAX(H14,J14)),))))

@Skybluekid Thank you so much it worked. This made my life much easier & hassle-free. Thank you for your support always. I have asked for help in many places but got the answer finally from you. I really appreciate it.

 

[Skybluekid]

No problem, happy to assist