Formula fill with absolute ($) referenced cells

R

RyanH002

New Member
Joined
Mar 31, 2021
Messages
16
Office Version
  1. 2021
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
 
Sort by date Sort by votes
jasonb75 said:
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.
Click to expand...
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
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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)
 
Upvote 0
jtakw said:
the missing 2nd quotes around the 2 "zzz
Click to expand...
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:
 
Upvote 0
jasonb75 said:
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)
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
jtakw said:
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.
Click to expand...
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.
 
Upvote 0
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)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Optimizing Price with Weightings to get to Best Price Mix - Use Solver?
Replies
1
Views
305
StephenCrump
S
K
  • Question Question
Populate With Criteria
Replies
3
Views
79
Peter_SSs
Peter_SSs
F
  • Solved
How to count the nth set of 6 numbers (repeated 6 numbers based on another column)
Replies
2
Views
181
feni1388
F
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
256
arun_eagertolearn
A
A
  • Locked
  • Question Question
Match customer and calculate commission basis their respective slabs
Replies
2
Views
307
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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
Back
Top