CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
pdobrev
pdobrev

Reputation: 3

Exclude specific cells from being included in a formula's calculation, even if said cells are referenced by the formula

I maintain a spreadsheet for a virtual racing competition. Within it, I have to log the times of each racer and how many points they have earned. I do so by importing a .csv file for each race (the import takes place on a secondary document, which is linked to the main one via DGET if that matters).

Screenshot of a spreadsheet page

In Column G, I calculate the total time for all races, via basic addition: =C5+E5, =C6+E6 etc.

But sometimes racers crash, which automatically replaces their time (HH:MM:SS.MsMs) in the .csv file, with a percentage value (%) denoting how far they got when they crashed. This causes the addition formula to spit out a wildly inaccurate result, as the software does its best to add a time value and a percentage value which represent a distance together.

Can I write a formula which only adds together the cells which have the proper time format?

So, for example, I set X99, Y99, and Z99, to be added together, but if Y99 contains a percentage value, it is excluded from the calculation, and the final output is the value of X99+Z99

Upvotes: 0

Views: 213

Answers (1)

Irene Liberali
Irene Liberali

Reputation: 401

Something like this?

= IF(REGEXMATCH(TO_TEXT(X99),"%"),0,X99)
+ IF(REGEXMATCH(TO_TEXT(Y99),"%"),0,Y99)
+ IF(REGEXMATCH(TO_TEXT(Z99),"%"),0,Z99)

It turns the number into a text string, then searches for a "%" in the string; if it finds one it adds 0, otherwise adds the time value.

Documentation:

Upvotes: 1

Related Questions