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
 

RyanH002

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
I'm now completely lost, from this line in post 1,

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.
This is absolutely my fault not explaining. You can completely ignore column A and B. I assume the countif can be moved to another column.

So let's start again as I have tried inserting all of your formulas but none of them seem to work. Possibly my fault. I have colour coded this to try and help explain my question further.

My question is this:
How do I fill down the formula in Cell H5 (Red cells), that has an absolute reference $G$5:$G$8, so that once it goes in to the blue cells the absolute reference changes to $G$9:$G$12 and then stays that reference for cells G9:G12 and then changes once again at G13 and so on.

The formula is currently as follows and works:
=IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-300/COUNTIF($G$5:$G$8,"<>"),IF(G5="WD",0)))))))))))))

The equation needs to remain, number of cells with data (in the 4 cell range as shown by the different colours) divided by -300. What I am currently having to do is fill down the formula (using the little square bottom right of active cell) and then having to go back and change the absolute referencing after. This is very time consuming as you can imagine.

Thank you for your help so far. I really hope this is more clear now? IGNORE column A and B. ;)

Fantasy Golf Help Screenshot.png




Fantasy Golf V3.xlsm
ABCDEFGHI
4PositionNamePlayerBet AmountOdds ValuePoints value For WinPlayer finishing PositionPoints AwardedTotal Points
52PGALife SimonDeChambeau90119906693543
6Ancer102502500MC-150
7
8
93PGALife AdamJohnson, D.5094503405485
10Rahm251230019180
11Mickelson251253125MC-100
12
131PGALife RyanSpieth3011330113303015
14Thomas3011330MC-75
15Westwood20408002760
16Poulter201252500301000
PGALife Boys
Cell Formulas
RangeFormula
F13:F16,F9:F11,F5:F6F5=E5*D5
H5:H6H5=IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-300/COUNTIF($G$5:$G$8,"<>"),IF(G5="WD",0)))))))))))))
I5,I13,I9I5=SUM(H5:H8)
H9:H11H9=IF(G9=1,F9+1000,IF(AND(G9=2),F9*95%,IF(AND(G9=3),F9*90%,IF(AND(G9=4),F9*85%,IF(AND(G9=5),F9*80%,IF(AND(G9>5,G9<11),F9*70%,IF(AND(G9>10,G9<21),F9*60%,IF(AND(G9>20,G9<31),F9*40%,IF(AND(G9>30,G9<41),F9*20%,IF(AND(G9>40,G9<51),F9*10%,IF(AND(G9>50,G9<100),0,IF(G9="MC",-300/COUNTIF($G$9:$G$12,"<>"),IF(G9="WD",0)))))))))))))
A5,A13,A9A5=RANK(I5,$I$5:$I$16)
H13:H16H13=IF(G13=1,F13+1000,IF(AND(G13=2),F13*95%,IF(AND(G13=3),F13*90%,IF(AND(G13=4),F13*85%,IF(AND(G13=5),F13*80%,IF(AND(G13>5,G13<11),F13*70%,IF(AND(G13>10,G13<21),F13*60%,IF(AND(G13>20,G13<31),F13*40%,IF(AND(G13>30,G13<41),F13*20%,IF(AND(G13>40,G13<51),F13*10%,IF(AND(G13>50,G13<100),0,IF(G13="MC",-300/COUNTIF($G$13:$G$16,"<>"),IF(G13="WD",0)))))))))))))
Cells with Data Validation
CellAllowCriteria
C5:C13List='Golfers List'!$A$2:$A$320
C14:C16List='Golfers List'!$A$2:$A$501
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
I found another typo in my formula that @jtakw didn't point out earlier, with that fixed it does work with the example provided. Although columns A and B are not part of the question, they are useful in identifying the ranges required. This assumes that each block (as identified by the different colours in the example) will consist of 4 rows. Without that as a control measure to identify the ranges, we would need something else to use for the same purpose, but I don't see that there is anything else that would work.

Book1
ABCDEFGHI
4PositionNamePlayerBet AmountOdds ValuePoints value For WinPlayer finishing PositionPoints AwardedTotal Points
52PGALife SimonDeChambeau90119906693543
6Ancer102502500MC-150
7 
8 
93PGALife AdamJohnson, D.5094503405485
10Rahm251230019180
11Mickelson251253125MC-100
12 
131PGALife RyanSpieth3011330113303015
14Thomas3011330MC-75
15Westwood20408002760
16Poulter201252500301000
Sheet4
Cell Formulas
RangeFormula
F5:F6,F13:F16,F9:F11F5=E5*D5
H5:H16H5=IF(C5="","",IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-300/COUNTIF(INDEX($G:$G,MATCH("zzz",B$1:B5)):INDEX($G:$G,MATCH("zzz",B$1:B5)+3),"<>"),IF(G5="WD",0))))))))))))))
I5,I9,I13I5=SUM(H5:H8)
A5,A9,A13A5=RANK(I5,$I$5:$I$16)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
I found another typo in my formula that @jtakw didn't point out earlier,
If you're talking about the missing 2nd quotes around the 2 "zzz ,I didn't mention it, but did fix it in Post # 9.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
the missing 2nd quotes around the 2 "zzz
That was it, I did see that you had corrected it when I checked back, but I didn't realise i=that the quotes were missing until I went back to my own post to copy it :oops:
 

RyanH002

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I found another typo in my formula that @jtakw didn't point out earlier, with that fixed it does work with the example provided. Although columns A and B are not part of the question, they are useful in identifying the ranges required. This assumes that each block (as identified by the different colours in the example) will consist of 4 rows. Without that as a control measure to identify the ranges, we would need something else to use for the same purpose, but I don't see that there is anything else that would work.

Book1
ABCDEFGHI
4PositionNamePlayerBet AmountOdds ValuePoints value For WinPlayer finishing PositionPoints AwardedTotal Points
52PGALife SimonDeChambeau90119906693543
6Ancer102502500MC-150
7 
8 
93PGALife AdamJohnson, D.5094503405485
10Rahm251230019180
11Mickelson251253125MC-100
12 
131PGALife RyanSpieth3011330113303015
14Thomas3011330MC-75
15Westwood20408002760
16Poulter201252500301000
Sheet4
Cell Formulas
RangeFormula
F5:F6,F13:F16,F9:F11F5=E5*D5
H5:H16H5=IF(C5="","",IF(G5=1,F5+1000,IF(AND(G5=2),F5*95%,IF(AND(G5=3),F5*90%,IF(AND(G5=4),F5*85%,IF(AND(G5=5),F5*80%,IF(AND(G5>5,G5<11),F5*70%,IF(AND(G5>10,G5<21),F5*60%,IF(AND(G5>20,G5<31),F5*40%,IF(AND(G5>30,G5<41),F5*20%,IF(AND(G5>40,G5<51),F5*10%,IF(AND(G5>50,G5<100),0,IF(G5="MC",-300/COUNTIF(INDEX($G:$G,MATCH("zzz",B$1:B5)):INDEX($G:$G,MATCH("zzz",B$1:B5)+3),"<>"),IF(G5="WD",0))))))))))))))
I5,I9,I13I5=SUM(H5:H8)
A5,A9,A13A5=RANK(I5,$I$5:$I$16)

Yes, Yes, YES! Thank you so much this is perfect!

Now wanting to understand the formula more. If you wouldnt mind, could you explain how the following parts of the formula work. I'm enjoying learning this, so would like to understand why it works.

1: IF(C5="",""
2: IF(G5="MC",-300/COUNTIF(INDEX($G:$G,MATCH("zzz",B$1:B5)):INDEX($G:$G,MATCH("zzz",B$1:B5)+3),"<>")

Again thank you so much for your help! It's much appreciated.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
The first part means that the formula is only calculated if there is a name in column C. If column C is empty then the formula will show blank.

The second part is your original formula with a dynamic range in countif. The bold part finds the start cell, the italic part finds the end cell.

INDEX($G:$G,MATCH("zzz",B$1:B5)):INDEX($G:$G,MATCH("zzz",B$1:B5)+3)

In both parts, MATCH("zzz" finds the name in column B that is in the same row, or if that row is empty, the nearest one above. When cells are merged, only the top left cell of the merged range has any value for formula purposes, the rest are empty. So in your sheet, B5 contains a name, B6:B8 are empty, B9 contains a name, B10:B12 are empty, and so on.

Because both parts are finding the position of the name, we add 3 to the end part to find the bottom of the merged cell. As I pointed out earlier, it will only work if all of the merged cells consist of 4 rows. Any more or less will cause it to fail and there is nothing else in your sheet (or at least in the example we have seen) which could be used to identify the end of each data block if the number of rows was variable.
 

jtakw

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

ADVERTISEMENT

Also, Have you tried my streamlined formula in Post #9 (modified from Post #7)?
You have a number of Un-needed AND statements in your IF formula.
 

RyanH002

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Also, Have you tried my streamlined formula in Post #9 (modified from Post #7)?
You have a number of Un-needed AND statements in your IF formula.
If you are referring to 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)))

I did try it but it kept coming up with an error.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
What's the error?
Did you adjust the cell references to match your data?

Book3.xlsx
ABCDEFGHI
31Richard HodgeSpieth2512300113006100
4Wallace25100250032250
5Woodland2515073651.1
6Burns25200MC-75
72Jonathan BonnerWoods2560150023600752
8D. Johnson301442014252
9Thomas5535MC-100
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/COUNTIF(INDEX(G:G,MATCH("zzz",B$1:B3)):INDEX(G:G,MATCH("zzz",B$1:B3)+3),"<>"),0)))
 

RyanH002

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
I’ll get back to you in the morning and let you know
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,154
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