Hi, Ive been trying unsuccessfully for a couple of hours to find out how to do the following:
I want to input multiple race results into a spreadsheet and get the total points for each competitor. If a competitor didnt finish the race I want to put “DNC” into the relevant cell and declare a variable DNC=10 so that the SUM function will read it as 10 points and add it to the other results.
I have named a cell DNC and it will work if I input it manually into the equation but I want to simply put DNC into the cell and have the system recognize it as a “variable”.
Is there a way?
Kind regards.
No way. Entering “DNC” into some cell you write the text “DNC”. The = sign initiates a non-constant entry, a formula. However, VLOOKUP can look up the meaning of the text DNC.
Thanks for the reply. Would it be possible to add an ‘if’ statement to the SUM equation so that if the cells have text it adds 10 to the sum?
Please don’t use answer for your text, if they are no answer to your question.
.
IF won’t help, but you may use COUNTIF to get the number of “DNC” and multiply this with your 10 points.
Maybe
=SUM(range)+COUNTIF(range;"DNC")*10
if “DNC” happens to be the only special string
Thanks guys.
Villeroy your code worked perfectly. Thank you so much.
Seems an awkward way of doing things as I would have thought this kind of thing would be fairly common on a spreadsheet and there would be a quick way of doing it.
Thanks again.
Since 40 years (Visicalc) things like this are fairly common to do in spreadsheets since spreadsheets allow strings and numbers in the same field. Mixed strings and numbers make everything complicated in any programming language.
1 Like
+COUNTIF(range;"DNC")*10
seems the code is shorter than your query for a quicker way ?
.
Other options would be to write =dnc to reference your named field or creating an second column (may be invisible) to get the value for “DNC” via VLOOKUP or an simple IF. This column can then be added by a SUM(range) directly.
1 Like