CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulamaxifs
Matthew Newton
Matthew Newton

Reputation: 7

How to add +1 to a value when another cell has a specific value?

I've written a formula in Google Sheets to calculate driver points for a sim-racing league I'm in. This formula looks at the driver's finishing position and produces a base point value corresponding to that finishing position. It then looks at the driver's number of Positions Gained during the race and adds 1 point if the driver had the most Positions Gained in their class (listed in another column). It then looks at the driver's starting position and adds an 1 point if the driver started the race in the highest position in their class. It then adds 2 points if the driver finished in the highest position in their class. The formula looks like this: =IFERROR(VLOOKUP(RaceFinishPosition,Points_System,2,FALSE)+IF(PosGained=MAXIFS(PosGained,Class,"Hypercar"),1,0)+IF(PosGained=MAXIFS(PosGained,Class,"GT3"),1,0)+IF(RaceFinishPosition=MINIFS(RaceFinishPosition,Class,"Hypercar"),2,0)+IF(RaceFinishPosition=MINIFS(RaceFinishPosition,Class,"GT3"),2,0)+IF(StartingPosition=MINIFS(StartingPosition,Class,"Hypercar"),1,0)+IF(StartingPosition=MINIFS(StartingPosition,Class,"GT3"),1,0),IF(ISNUMBER(StartingPosition),0,))

This works correctly most of the time. However, I am running into a few problems in specific cases. If none of the drivers in a class make any overtakes, i.e. they have 0 Positions Gained, the formula above gives everyone in that class 1 additional point since it reads that all drivers have the max value in the Positions Gained column. It is okay if two drivers get the additional points say if they both have 3 Positions Gained. I just don't want anyone getting points if everyone in a class finishes with 0 Positions Gained.

There is also a problem where if the two drivers with the most Positions Gained in the two different classes have the same number of Positions Gained. This causes each of those drivers to get +2 additional points instead of +1 (+1 point for the class they are in and +1 point for the other class.

I tried to add in different variations of things like (PosGained="Hypercar")and(PosGained="GT3") (The classes are named "Hypercar" and "GT3" respectively) to the IF function that adds the points for Positions Gained, but i haven't seemed to get it quite right yet. I've also tried to add some kind of logical expression to tell the formula to only add the +1 point for Positions Gained if the value in the Positions Gained column is >0, but I can't figure out where or how to fit that into the formula above. Any help would be greatly appreciated.

Here is a link to a sample sheet text. The sheet I am working on is "Week 1" and the formula is in column H tiled "Driver Points" in the table.

Thank you!

Someone provided this function =vlookup(G6,Overview!G$3:H$22,2,)+ if((E6<>0)*(E6=maxifs(E$6:E$18,C$6:C$18,C6)),1,)+ if(G6=minifs(D$6:D$18,C$6:C$18,C6),1,)+ if(G6=minifs(G$6:G$18,C$6:C$18,C6),2,) and it solved the problems mentioned in this post. However, it is not adding the points for best starting position correctly. Their answer went away (idk what happened sorry I'm new), but I've included explanations of what is wrong on the sheet. Thank you!

Upvotes: 0

Views: 209

Answers (2)

rockinfreakshow
rockinfreakshow

Reputation: 30289

You may try:

=vlookup(G6,Overview!G$3:H$22,2,)+
 if((E6<>0)*(E6=maxifs(E$6:E$18,C$6:C$18,C6)),1,)+
 if(D6=minifs(D$6:D$18,C$6:C$18,C6),1,)+
 if(G6=minifs(G$6:G$18,C$6:C$18,C6),2,)

Upvotes: 0

Matthew Newton
Matthew Newton

Reputation: 7

enter image description here

The answer, provided by @rockinfreakshow did it. I'm not sure where there answer went, so I apologize and thank them for their work and help. I had to tweak one small typo to get the Starting Position points to work correctly.

Here is the formula (again this was solved by user rockinfreakshow: =vlookup(G6,Overview!G$3:H$22,2,)+ if((E6<>0)*(E6=maxifs(E$6:E$18,C$6:C$18,C6)),1,)+ if(D6=minifs(D$6:D$18,C$6:C$18,C6),1,)+ if(G6=minifs(G$6:G$18,C$6:C$18,C6),2,)

Upvotes: 0

Related Questions