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
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
ABCDEGHI
310Richard HodgeCasey2035477074
4Koepka5028MC-75
5Thomas20116154
6Kuchar1090MC-75
73Jonathan BonnerMatsuyama25456787.52292.5
8Oosthuizen256640330
9Wallace2511040550
10Woodland2512532625
111AdzyZ 4000
12Lowry100100214000
13Z 
14Z 
152Ryan RozanskiMacIntyre251602116002330
16Fitzpatrick254521450
17Thomas25116192.5
18Casey25354787.5
195James WadeThomas301162311536
20Fitzpatrick204521360
21Hovland253317495
22Morikawa253013450
238Glenn TaylorSpieth30114280.5274.5
24Rahm301221144
25Koepka2028MC-75
26Westwood2040MC-75
276Marcus LeeCasey3035471051056
28Smith303513630
29Schauffele302212396
30Frittelli10150MC-75
319Julian RollRahm251221120120
32Simpson253540175
33McIlroy2518MC-75
34Smith253513525
354Ian ThomsonIm2535MC-1002077.5
36Casey25354787.5
37Kim2511061925
38Reed253332165
397Andrew KempGarcia5050MC-150750
40 
41Fitzpatrick504521900
42 
4312Mark MclaughlinMcIlroy10018MC-150-150
44
45
46
Viewers
Cell Formulas
RangeFormula
G43,G41,G15:G39,G12,G3:G10G3=VLOOKUP(C3,'Golfers List'!$C$2:$D$101,2)
H3:H43H3=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,A7A3=RANK(I3,$I$3:$I$58)
I3,I43,I39,I35,I27,I23,I19,I15,I11,I7I3=SUM(H3:H6)
I31I31=SUM(H31)
Cells with Data Validation
CellAllowCriteria
C3:C46List='Golfers List'!$A$2:$A$500
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

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

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

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

New Member
Joined
Mar 31, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Ok, all good, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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