Divide by count if Specific text.

RyanH002

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

I have quite a specific and in my mind complex, not only to write but explain.

I have a formula I want that counts the cells that have any information (I believe this to be a wildcard and uses "*") then use that number to multiply if that range of cells has an MC. But this needs to be relative across the 4 cells.

The premise is,
1 cell with content, MC = -300
2 cells, each MC = -150
3 cells, each MC = -100
4 cells, each MC = -75

The Points Awarded collumn already has this formula, which alson needs to be included:

=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",-100,IF(G5="WD",0)))))))))))))

Thanks for any help

1617231230762.png
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

I have used a VLOOKUP to simplify your formula. I'm not clear what you mean by relative across the 4 cells, but am guessing:

ABCDEFGHIJ
1
2MyLookup
3295%YoursMineAdjusted?
4390%30011,3001,3001,300
5485%87520525525525
6580%2,000MC-100-100-75
7670%12,50075000
81160%5503495495495
92140%1,750MC-100-100-75
103120%1,875MC-100-100-75
114110%
12510%475MC-100-100-75
Sheet1
Cell Formulas
RangeFormula
H4:H10,H12H4=IF(G4=1,F4+1000,IF(AND(G4=2),F4*95%,IF(AND(G4=3),F4*90%,IF(AND(G4=4),F4*85%,IF(AND(G4=5),F4*80%,IF(AND(G4>5,G4<11),F4*70%,IF(AND(G4>10,G4<21),F4*60%,IF(AND(G4>20,G4<31),F4*40%,IF(AND(G4>30,G4<41),F4*20%,IF(AND(G4>40,G4<51),F4*10%,IF(AND(G4>50,G4<100),0,IF(G4="MC",-100,IF(G4="WD",0)))))))))))))
I4:I10,I12I4=IF(G4=1,F4+1000,IF(G4="MC",-100,IF(G4="WD",0,F4*VLOOKUP(G4,MyLookup,2,1))))
J4:J10,J12J4=IF(G4=1,F4+1000,IF(G4="MC",-300/COUNTIF(G$4:G$12,"MC"),IF(G4="WD",0,F4*VLOOKUP(G4,MyLookup,2,1))))
Named Ranges
NameRefers ToCells
MyLookup=Sheet1!$B$3:$C$12I12:J12, I4:J10
 

RyanH002

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

Thank you for getting back to me so quickly.

I've managed to work this out with your help. I was trying to do the count of cells and then the division, but you showed me to do the look up of MC then countif...

=IF(G8="MC",-300/COUNTIF($G$5:$G$8,"<>"))

I'm not sure i understand the MyLookup bit on how to simplify the formula, but I'll work on that too. Thanks again.

Fantasy Golf V3.xlsm
ABCDEFGHI
4PositionPlayerBet AmountOdds ValuePoints value For WinPlayer finishing PositionPoints AwardedTotal Points
51SimonSpieth2512300113001525
6Tringale253587520525
7Dahmen25802000MC-300
8Lebioda2550012500750
92AdamConners25225503495-105
10Higgs25701750MC-300
11Howell25751875MC-300
12LandryFALSE
133RyanMorikawa2519475MC-300-300
14WoodlandFALSE
15WallaceFALSE
16Cabrera BelloFALSE
PGALife Boys
Cell Formulas
RangeFormula
H5:H16H5=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,IF(G5="WD",0)))))))))))))
I5,I13,I9I5=SUM(H5:H8)
F13,F5:F11F5=E5*D5
A5,A13,A9A5=RANK(I5,$I$5:$I$16)
Cells with Data Validation
CellAllowCriteria
C5:C13List='Golfers List'!$A$2:$A$319
C14:C16List='Golfers List'!$A$2:$A$500
 

Watch MrExcel Video

Forum statistics

Threads
1,129,568
Messages
5,637,093
Members
416,957
Latest member
Brovashift

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