Greater than but not equal to for Google Sheets.

[joshparry6]

Hi,

I need help with a formula for when a cell is greater than other cells in a range, but not equal to any of them - a uniquely higher number.

This is for a golf betting game whereby the winner of each hole wins however many yards the hole is in pence.

If no one wins the hole outright the money will carry over to the next hole (I'm comfortable with that).

The formula I have in G3 needs to output '0' or 'blank' as golfer A & B both have the same score - meaning the money will then roll over to the next hole.

What would the formula in cell D4 need to be?


Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
In future when posting questions for apps other than Excel, you need to post the question in the General Discussion & Other Applications section of the board & make it clear what app you're using.
Thanks

 

[Fluff]

How about

=IF(COUNTIFS(C3:F3,MAX(C3:F3))=1,IF(C3=MAX(C3:F3),B3/100,0),0)

 

[joshparry6]

How about

=IF(COUNTIFS(C3:F3,MAX(C3:F3))=1,IF(C3=MAX(C3:F3),B3/100,0),0)

That's perfect thank you! Where would I add the 'IF(G2<>"N"' within that formula?

 

[Fluff]

How about

=IF(OR(G2="N",COUNTIFS(C3:F3,MAX(C3:F3))>1),0,IF(C3=MAX(C3:F3),B3/100,0))

 

[joshparry6]

How about

=IF(OR(G2="N",COUNTIFS(C3:F3,MAX(C3:F3))>1),0,IF(C3=MAX(C3:F3),B3/100,0))

Thank you so much! Really appreciate it!!

 

[Snakehips]

What about the following to take into account any accumulated money?
Formula in G3 and drag across and down.

Book1
	A	B	C	D	E	F	G	H	I	J
1				Score				Money
2	Hole	Yardage	A	B	C	N	A	B	C	N
3	1	308	3	1	0	0	3.08	0	0	0
4	2	425	0	2	1	0	0	4.25	0	0
5	3	394	3	2	2	0	3.94	0	0	0
6	4	123	3	3	3	3	0	0	0	0
7	5	324	4	4	4	4	0	0	0	0
8	6	564	2	2	3	2	0	0	10.11	0
Sheet2

Cell Formulas
Range	Formula
G3:J8	G3	=IF(AND(COUNTIF($C3:$F3,MAX($C3:$F3))=1,C3=MAX($C3:$F3)),(SUM($B$3:$B3)/100)-SUM($G$2:$J2),0)

 

[Fluff]

You're welcome & thanks for the feedback.

 

[joshparry6]

What about the following to take into account any accumulated money?
Formula in G3 and drag across and down.

That's perfect, I was struggling to figure out how to calculate the accumulated money. Thank you so much!