formulasgoogle sheets
Using Google Forms for a quiz…
The quiz is graded /30.
Value is transmitted into Google Sheets.
Cell C2 displays a value of grade… here, 14/30 (as an example)
Value for cell C2 reads as 14
Attempting to get the value of D2 to show a mark on 100…
=C2/30 (this should give me 46.6%) but it returns a value of 0
Why won't it pick up the value of C2 as the value 14 it shows in the fx-box?
How can I get it to pick up the value from the cell to then perform more functions?
You have to format the column in spreadsheet mode before any data is entered.
Here we have a form about waffles. The cells have been formatted before sending it to the recipients.
And here is the result after a user fills out the result.
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
Best Answer
If the cell values are entered as numbers, then you will be able to perform the functions you are requesting. I would first double-check to ensure that the values that are within the spreadsheet are formatted as numbers.
I have had some difficulty with performing basic functions like the one you have presented only to find out that the spreadsheet is not treating the values in the cell as the appropriate type of data.
Next, I would then check the number of decimals the function is being rounded to. If the rounding is to the 0th decimal place, then you will not see a result for the function =14/30. That function would result in the answer of 0.466667, which would be rounded to 0.