Formula fill with absolute ($) referenced cells

[RyanH002]

Hello all,

I have a fantasy golf game with my friends and I have a formula to work out the points (I know it's long, haven't worked out how to use my lookup). There's a part of the formula that requires to count the number of cells using the countif function and then work out the result from there.

I made it an absolute cell reference which works fine. My question is the following:

How do I fill the formula down to the next player and have the countif reference move too?

Fantasy Golf V3.xlsm
	A	B	C	D	E		G	H	I
3	1	Richard Hodge	Spieth	25	12		1	1300	6100
4			Wallace	25	100		3	2250
5			Woodland	25	150		6	2625
6			Burns	25	200		MC	-75
7	2	Jonathan Bonner	Woods	25	60		23	600	752
8			D. Johnson	30	14		14	252
9			Thomas	55	35		MC	-100
10
Viewers

Cell Formulas
Range	Formula
G7:G9,G3:G5	G3	=VLOOKUP(C3,'Golfers List'!$C$2:$D$37,2)
H3:H6	H3	=IF(G3=1,F3+1000,IF(AND(G3=2),F3*95%,IF(AND(G3=3),F3*90%,IF(AND(G3=4),F3*85%,IF(AND(G3=5),F3*80%,IF(AND(G3>5,G3<11),F3*70%,IF(AND(G3>10,G3<21),F3*60%,IF(AND(G3>20,G3<31),F3*40%,IF(AND(G3>30,G3<41),F3*20%,IF(AND(G3>40,G3<51),F3*10%,IF(AND(G3>50,G3<100),0,IF(G3="MC",-300/COUNTIF($G$3:$G$6,"<>"),IF(G3="WD",0)))))))))))))
A3,A7	A3	=RANK(I3,$I$3:$I$10)
H7:H9	H7	=IF(G7=1,F7+1000,IF(AND(G7=2),F7*95%,IF(AND(G7=3),F7*90%,IF(AND(G7=4),F7*85%,IF(AND(G7=5),F7*80%,IF(AND(G7>5,G7<11),F7*70%,IF(AND(G7>10,G7<21),F7*60%,IF(AND(G7>20,G7<31),F7*40%,IF(AND(G7>30,G7<41),F7*20%,IF(AND(G7>40,G7<51),F7*10%,IF(AND(G7>50,G7<100),0,IF(G7="MC",-300/COUNTIF($G$7:$G$10,"<>"),IF(G7="WD",0)))))))))))))
I3,I7	I3	=SUM(H3:H6)

Cells with Data Validation
Cell	Allow	Criteria
C3:C10	List	='Golfers List'!$A$2:$A$322

 

[RyanH002]

Hi,

So I've come across an error in here that I don't understand...

Cell H43 should read -300 as there is only 1 data value within the 4 value range. However, if I put a MC in to cell H44, then the value in H39 changes...

I am open to unmerging column A and at last resort B if this helps with matters?

Fantasy Golf V3.xlsm
	A	B	C	D	E		G	H	I
3	10	Richard Hodge	Casey	20	35		47	70	74
4			Koepka	50	28		MC	-75
5			Thomas	20	11		6	154
6			Kuchar	10	90		MC	-75
7	3	Jonathan Bonner	Matsuyama	25	45		6	787.5	2292.5
8			Oosthuizen	25	66		40	330
9			Wallace	25	110		40	550
10			Woodland	25	125		32	625
11	1	Adzy					Z		4000
12			Lowry	100	100		21	4000
13							Z
14							Z
15	2	Ryan Rozanski	MacIntyre	25	160		21	1600	2330
16			Fitzpatrick	25	45		21	450
17			Thomas	25	11		6	192.5
18			Casey	25	35		47	87.5
19	5	James Wade	Thomas	30	11		6	231	1536
20			Fitzpatrick	20	45		21	360
21			Hovland	25	33		17	495
22			Morikawa	25	30		13	450
23	8	Glenn Taylor	Spieth	30	11		4	280.5	274.5
24			Rahm	30	12		21	144
25			Koepka	20	28		MC	-75
26			Westwood	20	40		MC	-75
27	6	Marcus Lee	Casey	30	35		47	105	1056
28			Smith	30	35		13	630
29			Schauffele	30	22		12	396
30			Frittelli	10	150		MC	-75
31	9	Julian Roll	Rahm	25	12		21	120	120
32			Simpson	25	35		40	175
33			McIlroy	25	18		MC	-75
34			Smith	25	35		13	525
35	4	Ian Thomson	Im	25	35		MC	-100	2077.5
36			Casey	25	35		47	87.5
37			Kim	25	110		6	1925
38			Reed	25	33		32	165
39	7	Andrew Kemp	Garcia	50	50		MC	-150	750
40
41			Fitzpatrick	50	45		21	900
42
43	12	Mark Mclaughlin	McIlroy	100	18		MC	-150	-150
44
45
46
Viewers

Cell Formulas
Range	Formula
G43,G41,G15:G39,G12,G3:G10	G3	=VLOOKUP(C3,'Golfers List'!$C$2:$D$101,2)
H3:H43	H3	=IF(C3="","",IF(G3=1,F3+1000,IF(AND(G3=2),F3*95%,IF(AND(G3=3),F3*90%,IF(AND(G3=4),F3*85%,IF(AND(G3=5),F3*80%,IF(AND(G3>5,G3<11),F3*70%,IF(AND(G3>10,G3<21),F3*60%,IF(AND(G3>20,G3<31),F3*40%,IF(AND(G3>30,G3<41),F3*20%,IF(AND(G3>40,G3<51),F3*10%,IF(AND(G3>50,G3<100),0,IF(G3="MC",-300/COUNTIF(INDEX($G:$G,MATCH("zzz",B$3:B7)):INDEX($G:$G,MATCH("zzz",B$3:B7)+3),"<>"),IF(G3="WD",0))))))))))))))
A3,A43,A39,A35,A31,A27,A23,A19,A15,A11,A7	A3	=RANK(I3,$I$3:$I$58)
I3,I43,I39,I35,I27,I23,I19,I15,I11,I7	I3	=SUM(H3:H6)
I31	I31	=SUM(H31)

Cells with Data Validation
Cell	Allow	Criteria
C3:C46	List	='Golfers List'!$A$2:$A$500

 

[jasonb75]

The match range is extending too far down the column, the range for the formula in H3 should be either B$1:B3, or B$3:B3 (doesn't matter which), the main point is that bot match ranges must end at the row with the formula, not be extended to the end of the block. The +3 on the second one is there for that.

As you have just discovered, using the wrong range gives wrong results.

Also, this would work much better if you used @jtakw's shorter formula, the one that you have is messy, making errors much harder to find.

 

[RyanH002]

I see I had the $ on the wrong cell. All sorted now and thank you for your help. Very much appreciated.

I have also managed to update the formula to
=IF(G3=1,F3+1000,IFERROR(F3*LOOKUP(G3,{2,3,4,5,6,11,21,31,41,51},{0.95,0.9,0.85,0.8,0.7,0.6,0.4,0.2,0.1,0}),IF(G3="MC",-300/COUNTIF(INDEX($G:$G,MATCH("zzz",B$1:B3)):INDEX($G:$G,MATCH("zzz",B$1:B3)+3),"<>"),IF(G3="WD",0))))

 

[jtakw]

Glad you worked it all out.

Just a note, by adding IF(G3="WD",0) at the end of my formula, instead of just 0 (zero) like in my version, if G3 is Blank or something other than a number or MC, you'll get FALSE as a result.
In my formula, that last part is just 0 (zero), cause I figured, if G3 is Not a number, Not MC, then return 0, is that not your intention?

 

[RyanH002]

Yes a FALSE is good for me as it's an instant indication that there is a cell missing data. But thank you for bringing it up.

You've been awesome with all your help and most of all patience! Thank you!

 

[jtakw]

Ok, all good, thanks for the feedback.