=ifs or =if(or(

[Mel Smith]

Hello folks,

Could someone help me with this formula, please? I have two columns containing values (C4:C37) and (E4:E37). What I am trying (and failing) to achieve is in G4 (and I will copy it down to G37) is if the value in C4 or E4 is the Max value of C4:C37 OR E4:E37 then display that value, otherwise show nothing ("").

Many thanks.

Mel

 

[JoeMo]

In cells G4:G37 I'm trying to get the highest value from cells C4:C37 and/or cells E4:E37. In G4, for example, if the highest values from C4:C37 AND E4:E37 is not in either C4 or E4 then display "" and then have this copied down to G37. If, say the highest value in column C was 25 and the highest value in column E was 24 then the only value I want to display in G# will be 25 (the highest overall.

I hope this makes sense.

Mel

I think that's exactly what the formula is doing. If you are getting a result contrary to that, please post the data associated with that result using XL2BB.

 

[Mel Smith]

Here's what my spreadsheet looks like:

1st XI dot ball strings.xlsm
	C	D	E	F	G
4	1		1
5	2		6
6	2		9
7	4		12
8	16		2		16
9	4		4
10	5		5
11	6		6
12	7		7
13	8		8
14	9		9
15
16
17
18
19
20
21
22
23
24			15		16
25
26
27
28			15		16
29
30	10		12
31
32
33
34
35
36
37	10		13
First Class

Cell Formulas
Range	Formula
G4:G28	G4	=IF(OR(C4=MAX($C$4:$C$37),E4=MAX($E$4:$E$37)),MAX($C$4:$E$37),"")
G29:G37	G29	=IF(OR(C29=MAX($C$4:$C$37),E29=MAX($E$4:$E$37)),MAX($C$4:$C$37,$E$4:$E$37),"")

Named Ranges
Name	Refers To	Cells
Output_1	='First Class'!$D$4:$D$37,'First Class'!$F$4:$F$37	G4:G28

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:E37	Other Type	Color scale		NO
C4:C37	Other Type	Color scale		NO

Cells with Data Validation
Cell	Allow	Criteria
D4:D37	List	=Squad
F4:F37	List	=Squad

 

[JoeMo]

Looks like the formula is returning exactly what is intended. What's your concern?

If, say the highest value in column C was 25 and the highest value in column E was 24 then the only value I want to display in G# will be 25 (the highest overall.

 

[Mel Smith]

On line 24 and 28 the display in G24 and G28 should be "" because there is only one value of 16 (in cell C8) therefore all cells in column G should be blank except for G8.

 

[JoeMo]

On line 24 and 28 the display in G24 and G28 should be "" because there is only one value of 16 (in cell C8) therefore all cells in column G should be blank except for G8.

Not so. Wherever the value of the col E cell matches the max value in col E cells, the max value in G4:E37 will be returned.

Perhaps this is what you want?

Book1
	C	D	E	F	G
4	1		1
5	2		6
6	2		9
7	4		12
8	16		2		16
9	4		4
10	5		5
11	6		6
12	7		7
13	8		8
14	9		9
15
16
17
18
19
20
21
22
23
24			15
25
26
27
28			15
29
30	10		12
31
32
33
34
35
36
37	10		13
IQData

Cell Formulas
Range	Formula
G4:G37	G4	=IF(C4=MAX($C$4:$E$37),C4,IF(E4=MAX($C$4:$E$37),E4,""))

 

[Mel Smith]

Hi Joe,

That's just the job! Many thanks, as always.

Mel

 

[JoeMo]

Hi Joe,

That's just the job! Many thanks, as always.

Mel

You are welcome - thanks for the reply.

 

[srehman]

Your question is not clear