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

 

[Joe4]

This is another example of why merged cells are generally a bad idea. Most experienced programmers will tell you to avoid them at all costs - they will cause you nothing but problems (for things like sorting, VBA, etc).

If you had the name in column B on each row, then you could probably use COUNTIFS, and check the value in column B to limit it to just the records you want for each person.

Alternatively, you might be better off trying to set up something like a Pivot Table to track this kind of information, if you are going to use Excel to do it (as opposed to Microsoft Access or some other database tool). I don't use Pivot Tables much in my line of work, but have seen people use them for these sorts of things.

 

[jasonb75]

As it seems to be only the countif section that needs to change, try this method. This assumes that it will always be 4 rows per player.

As pointed out above, your layout is far from ideal, not every problem that you encounter will be so easily solvable.

COUNTIF(INDEX($G:$G,MATCH("zzz,$A1:$A3)):INDEX($G:$G,MATCH("zzz,$A1:$A3)+3),"<>")

 

[RyanH002]

Yes there will always be a minimum of 1 and up to four picks per player.

Can you please explain this formula in more detail? What do the "zzz" refer to and will I be able to fill down this formula?

Even if the cells weren't merged like below, the issue remains the same in my eyes, but I am quite a novice at excell.

Thanks for your help and input so far.

Fantasy Golf V3.xlsm
	C	D	E		G	H	I
3	Almulla	25	12		1	1300	2772.5
4	An	25	50		2	1187.5
5	Ancer	25	16		3	360
6	Anderson	25	100		MC	-75
7	Anderson	25	100		4	2125	2637.5
8	Armour	25	35		7	612.5
9	Appleby	50	60		MC	-100
10
Viewers

Cell Formulas
Range	Formula
G3:G5	G3	=VLOOKUP(C3,'Golfers List'!$B$2:$C$11,2)
I3,I7	I3	=SUM(H3:H6)
G7:G8	G7	=VLOOKUP(C7,'Golfers List'!$B$2:$C$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)))))))))))))
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)))))))))))))

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

 

[jasonb75]

"zzz" with an approximate match finds the last rows with text data in the specified range. With merged cells, only the top left cell of the merge are contains anything, the rest are empty.

I just realised that I had referenced the wrong column in my suggestion and that it should be

COUNTIF(INDEX($G:$G,MATCH("zzz,$B1:$B3)):INDEX($G:$G,MATCH("zzz,$B1:$B3)+3),"<>")

In short, it will start from the top of the current merged cell in column B, then add 3 rows for the end of the range (4 rows total).

Without merged cells, the references in column A and / or column B should be populated to all rows for the same player, giving a key for the formula to use.

 

[RyanH002]

Ok, excuse my ignorance, but the Names in Column B have no reference on the formula and are there to indicate who has picked which golfers and what their bet is.

My want to fill down a formula with absolute reference to 4 cells at a time has no reflection on any column other than G. The purpose of the countif part of the formula is as follows:
If a player only picks one golfer and they MC then they get -300, 2 golfers and each MC = -150, 3 golfers and each MC = -100 and 4 golfers each MC = -75. As each player can pick between 1 and 4 golfers, the countif works this out. I then want to be able to fill down the formula to the rest of the players and not have to adjust the absolute reference of the 4 cells in column G.

If what you have said above does do this then thank you for you help, but I don't understand it... if not, then I continue to welcome your advice. I've attched more of the sheet to help

Fantasy Golf V3.xlsm
	B	C	D	E		G	H	I
317	Richard Hodge	Kirk	30	40		6	840	3180
318		Kuchar	40	60		12	1440
319		Hadwin	30	75		23	900
320
321	Jonathan Bonner	Hoffman	25	30		2	712.5	1262.5
322		Stadler	25	2000		54	0
323		Cabrera Bello	25	250		44	625
324		Harrington	25	350		MC	-75
325	Adzy	Champ	40	110		34	880	2590
326
327		Hoffman	60	30		2	1710
328
329	Ryan Rozanski	Willett	25	90		MC	-75	1020
330		Knox	25	125		MC	-75
331		Palmer	25	28		17	420
332		Hadwin	25	75		23	750
333	James Wade	Scheffler	30	16		54	0	1080
334		Conners	25	22		14	330
335		Bradley	25	60		23	600
336		Wise	20	75		44	150
337	Glenn Taylor	Spieth	30	12		1	1360	3296
338		Conners	30	22		14	396
339		Kizzire	20	110		9	1540
340		Kang	20	500		59	0
Viewers Old

Cell Formulas
Range	Formula
I317,I337,I333,I329,I325,I321	I317	=SUM(H317:H320)
H317:H319	H317	=IF(G317=1,F317+1000,IF(AND(G317=2),F317*95%,IF(AND(G317=3),F317*90%,IF(AND(G317=4),F317*85%,IF(AND(G317=5),F317*80%,IF(AND(G317>5,G317<11),F317*70%,IF(AND(G317>10,G317<21),F317*60%,IF(AND(G317>20,G317<31),F317*40%,IF(AND(G317>30,G317<41),F317*20%,IF(AND(G317>40,G317<51),F317*10%,IF(AND(G317>50,G317<100),0,IF(G317="MC",-300/COUNTIF($G$317:$G$320,"<>"),IF(G317="WD",0)))))))))))))
H321:H324	H321	=IF(G321=1,F321+1000,IF(AND(G321=2),F321*95%,IF(AND(G321=3),F321*90%,IF(AND(G321=4),F321*85%,IF(AND(G321=5),F321*80%,IF(AND(G321>5,G321<11),F321*70%,IF(AND(G321>10,G321<21),F321*60%,IF(AND(G321>20,G321<31),F321*40%,IF(AND(G321>30,G321<41),F321*20%,IF(AND(G321>40,G321<51),F321*10%,IF(AND(G321>50,G321<100),0,IF(G321="MC",-300/COUNTIF($G$321:$G$324,"<>"),IF(G321="WD",0)))))))))))))
H325	H325	=IF(G325=1,F325+1000,IF(AND(G325=2),F325*95%,IF(AND(G325=3),F325*90%,IF(AND(G325=4),F325*85%,IF(AND(G325=5),F325*80%,IF(AND(G325>5,G325<11),F325*70%,IF(AND(G325>10,G325<21),F325*60%,IF(AND(G325>20,G325<31),F325*40%,IF(AND(G325>30,G325<41),F325*20%,IF(AND(G325>40,G325<51),F325*10%,IF(AND(G325>50,G325<100),0,IF(G325="MC",-300/COUNTIF($G$325:$G$328,"<>"),IF(G325="WD",0)))))))))))))
H327	H327	=IF(G327=1,F327+1000,IF(AND(G327=2),F327*95%,IF(AND(G327=3),F327*90%,IF(AND(G327=4),F327*85%,IF(AND(G327=5),F327*80%,IF(AND(G327>5,G327<11),F327*70%,IF(AND(G327>10,G327<21),F327*60%,IF(AND(G327>20,G327<31),F327*40%,IF(AND(G327>30,G327<41),F327*20%,IF(AND(G327>40,G327<51),F327*10%,IF(AND(G327>50,G327<100),0,IF(G327="MC",-300/COUNTIF($G$11:$G$14,"<>"),IF(G327="WD",0)))))))))))))
H329:H332	H329	=IF(G329=1,F329+1000,IF(AND(G329=2),F329*95%,IF(AND(G329=3),F329*90%,IF(AND(G329=4),F329*85%,IF(AND(G329=5),F329*80%,IF(AND(G329>5,G329<11),F329*70%,IF(AND(G329>10,G329<21),F329*60%,IF(AND(G329>20,G329<31),F329*40%,IF(AND(G329>30,G329<41),F329*20%,IF(AND(G329>40,G329<51),F329*10%,IF(AND(G329>50,G329<100),0,IF(G329="MC",-300/COUNTIF($G$329:$G$332,"<>"),IF(G329="WD",0)))))))))))))
H333:H336	H333	=IF(G333=1,F333+1000,IF(AND(G333=2),F333*95%,IF(AND(G333=3),F333*90%,IF(AND(G333=4),F333*85%,IF(AND(G333=5),F333*80%,IF(AND(G333>5,G333<11),F333*70%,IF(AND(G333>10,G333<21),F333*60%,IF(AND(G333>20,G333<31),F333*40%,IF(AND(G333>30,G333<41),F333*20%,IF(AND(G333>40,G333<51),F333*10%,IF(AND(G333>50,G333<100),0,IF(G333="MC",-300/COUNTIF($G$333:$G$336,"<>"),IF(G333="WD",0)))))))))))))
H337:H339	H337	=IF(G337=1,F337+1000,IF(AND(G337=2),F337*95%,IF(AND(G337=3),F337*90%,IF(AND(G337=4),F337*85%,IF(AND(G337=5),F337*80%,IF(AND(G337>5,G337<11),F337*70%,IF(AND(G337>10,G337<21),F337*60%,IF(AND(G337>20,G337<31),F337*40%,IF(AND(G337>30,G337<41),F337*20%,IF(AND(G337>40,G337<51),F337*10%,IF(AND(G337>50,G337<100),0,IF(G337="MC",-300/COUNTIF($G$23:$G$26,"<>"),IF(G337="WD",0)))))))))))))
H340	H340	=IF(G340=1,F340+1000,IF(AND(G340=2),F340*95%,IF(AND(G340=3),F340*90%,IF(AND(G340=4),F340*85%,IF(AND(G340=5),F340*80%,IF(AND(G340>5,G340<11),F340*70%,IF(AND(G340>10,G340<21),F340*60%,IF(AND(G340>20,G340<31),F340*40%,IF(AND(G340>30,G340<41),F340*20%,IF(AND(G340>40,G340<51),F340*10%,IF(AND(G340>50,G340<100),0,IF(G340="MC",-300/COUNTIF($G$337:$G$340,"<>"),IF(G340="WD",0)))))))))))))

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

 

[jtakw]

Hi,

If you're willing to make a small change to Column A ( It could be Column B done similarly, but with names ), this may work for you.

You had Column F hidden, so those are just my guess:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
3	1	Richard Hodge	Spieth	25	12	300	1	1300	6100
4	1		Wallace	25	100	2500	3	2250
5	1		Woodland	25	150	73	6	51.1
6	1		Burns	25	200		MC	-75
7	2	Jonathan Bonner	Woods	25	60	1500	23	600	752
8	2		D. Johnson	30	14	420	14	252
9	2		Thomas	55	35		MC	-100
10
Sheet896

Cell Formulas
Range	Formula
H3:H9	H3	=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/COUNTIFS(G$3:G$10,"<>",A$3:A$10,A3),0)))

 

[jasonb75]

I'm now completely lost, from this line in post 1,

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

I had read it that the names in column B were the players and that the countif range was meant to run parallel to the merged cells containing the name associated with the current row.

 

[jtakw]

"zzz" with an approximate match finds the last rows with text data in the specified range. With merged cells, only the top left cell of the merge are contains anything, the rest are empty.

I just realised that I had referenced the wrong column in my suggestion and that it should be

COUNTIF(INDEX($G:$G,MATCH("zzz,$B1:$B3)):INDEX($G:$G,MATCH("zzz,$B1:$B3)+3),"<>")

In short, it will start from the top of the current merged cell in column B, then add 3 rows for the end of the range (4 rows total).

Without merged cells, the references in column A and / or column B should be populated to all rows for the same player, giving a key for the formula to use.

Ok, excuse my ignorance, but the Names in Column B have no reference on the formula and are there to indicate who has picked which golfers and what their bet is.

My want to fill down a formula with absolute reference to 4 cells at a time has no reflection on any column other than G. The purpose of the countif part of the formula is as follows:
If a player only picks one golfer and they MC then they get -300, 2 golfers and each MC = -150, 3 golfers and each MC = -100 and 4 golfers each MC = -75. As each player can pick between 1 and 4 golfers, the countif works this out. I then want to be able to fill down the formula to the rest of the players and not have to adjust the absolute reference of the 4 cells in column G.

If what you have said above does do this then thank you for you help, but I don't understand it... if not, then I continue to welcome your advice. I've attched more of the sheet to help

The formula by @jasonb75 in Post #5 should work to replace the COUNTIF part of your current formula, although there are a couple of "misplaced" absolute references, here's what I believe to be the corrected version:

=COUNTIF(INDEX(G:G,MATCH("zzz",B$1:B3)):INDEX(G:G,MATCH("zzz",B$1:B3)+3),"<>")

Try replacing your COUNTIF inside your current formula with the above, it should work.

Another note, if you do Not want to take my suggestion in Post #7 regarding populating Column A blanks with the associated numbers (You can hide this column if you like), then you can also replace my COUNTIF part of my formula with jason75's suggestion like this:

=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),"<>"),0)))

 

[jasonb75]

although there are a couple of "misplaced" absolute references

Oops, my bad