Formula fill with absolute ($) referenced cells

RyanH002

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEGHI
31Richard HodgeSpieth2512113006100
4Wallace2510032250
5Woodland2515062625
6Burns25200MC-75
72Jonathan BonnerWoods256023600752
8D. Johnson301414252
9Thomas5535MC-100
10
Viewers
Cell Formulas
RangeFormula
G7:G9,G3:G5G3=VLOOKUP(C3,'Golfers List'!$C$2:$D$37,2)
H3:H6H3=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,A7A3=RANK(I3,$I$3:$I$10)
H7:H9H7=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,I7I3=SUM(H3:H6)
Cells with Data Validation
CellAllowCriteria
C3:C10List='Golfers List'!$A$2:$A$322
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,948
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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
CDEGHI
3Almulla2512113002772.5
4An255021187.5
5Ancer25163360
6Anderson25100MC-75
7Anderson25100421252637.5
8Armour25357612.5
9Appleby5060MC-100
10
Viewers
Cell Formulas
RangeFormula
G3:G5G3=VLOOKUP(C3,'Golfers List'!$B$2:$C$11,2)
I3,I7I3=SUM(H3:H6)
G7:G8G7=VLOOKUP(C7,'Golfers List'!$B$2:$C$37,2)
H3:H6H3=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:H9H7=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
CellAllowCriteria
C3:C10List='Golfers List'!$A$2:$A$500
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

"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
Excel Formula:
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

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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
BCDEGHI
317Richard HodgeKirk304068403180
318Kuchar4060121440
319Hadwin307523900
320
321Jonathan BonnerHoffman25302712.51262.5
322Stadler252000540
323Cabrera Bello2525044625
324Harrington25350MC-75
325AdzyChamp40110348802590
326
327Hoffman603021710
328
329Ryan RozanskiWillett2590MC-751020
330Knox25125MC-75
331Palmer252817420
332Hadwin257523750
333James WadeScheffler30165401080
334Conners252214330
335Bradley256023600
336Wise207544150
337Glenn TaylorSpieth3012113603296
338Conners302214396
339Kizzire2011091540
340Kang20500590
Viewers Old
Cell Formulas
RangeFormula
I317,I337,I333,I329,I325,I321I317=SUM(H317:H320)
H317:H319H317=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:H324H321=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)))))))))))))
H325H325=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)))))))))))))
H327H327=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:H332H329=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:H336H333=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:H339H337=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)))))))))))))
H340H340=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
CellAllowCriteria
C317:C340List='Golfers List'!$A$2:$A$322
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHI
31Richard HodgeSpieth2512300113006100
41Wallace25100250032250
51Woodland2515073651.1
61Burns25200MC-75
72Jonathan BonnerWoods2560150023600752
82D. Johnson301442014252
92Thomas5535MC-100
10
Sheet896
Cell Formulas
RangeFormula
H3:H9H3=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
"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
Excel Formula:
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:

Excel Formula:
=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:

Excel Formula:
=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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top