0

I am creating a lab for students where they enter data into a spreadsheet that, using conditional values, will tell them if their values and precision are correct. For example: The sample volume is 22.4 ml.

I would like the cell to turn green if the volume they type in is between 22.1 and 22.5 (I already know how to do this with conditional formatting)

I would like the cell to turn red if they are outside the 22.1 to 22.5 range (I can do this too) OR they don't have the correct decimal place. Example 22 ml would be red. OR 22.22 ml would be red (this is what I do not know how to do).

I do not want the cell to automatically round their number, I am trying to test whether understand that the graduated cylinder must be read to the tenths place.

Thanks!!

0

2 Answers 2

1

As Google Sheets will remove the trailing zeros. Ask your students to prepend a ' (apostrophe, single quote character) to force Google Sheets to keep the value exactly as the student entered it, then use a custom formula in your conditional formatting rules. You might use functions that handle regular expressions like REXEXMATCH.

Prepending a value with ' will force Google Sheets to handle the value as text in some contexts. Consider this if the values entered are required in formulas as numbers. If the values are not cast as numbers automatically, you might use VALUE, N and TO_PURE_NUMBER.


Most numbers entered into cells using spreadsheet apps like Google Sheets, Excel and others will not be rounded automatically. Spreadsheet apps have limits about the numbers they can handle, but the numbers shown in the question are inside the number limits that Google Sheets can handle without rounding them.

Spreadsheet cells display numbers according to the cell number formatting. The cell formatting might vary for many reasons. Still, despite the value displayed, the number used in calculations, including conditional formatting, if this is between the limits that could be handled without rounding, the number entered by the user is the one that will be used.

You should worry about the number limits if you work in finance, engineering, physics and other disciplines requiring high accuracy and precision.

An alternative to asking the students to prepend the inputs with ' is to set requirements for the cells to enter numbers and present results. You could include this in the lab for students rubric.

Depending on the lab learning goals, you might find using another app, like Google Forms, easier than Google Sheets to avoid dealing with spreadsheet quirks.

Disclaimer: I use Wicket on Super User and other Stack Exchange sites. I'm no longer using it here. There might be more details in my user profile.

0

MOD

This approach uses the MOD function to test if a number meets your level of precision. It relies on valid numbers always having a non-zero digit in the position of precision.

Formula

Precision of one decimal place

Returns TRUE if n is a decimal number whose whole-number part is anything and whose decimal-number part is a single non-zero digit followed by zero or more zeros.

=MOD(n,1)*NOT(MOD(n,0.1))

Example Results

n Formula Result
2.1 TRUE
2.10 TRUE
0.1 TRUE
.1 TRUE
2 FALSE
2.0 FALSE
2.11 FALSE
2.01 FALSE
2.101 FALSE

Full Formula

=MOD(n,1)*NOT(MOD(n,0.1))*
    (n>=22.1)*(n<=22.5)
n Full Formula Result
22.1 TRUE
22.10 TRUE
22.100 TRUE
22.6 FALSE
22 FALSE
22.0 FALSE
22.11 FALSE
22.01 FALSE
22.101 FALSE
3
  • If I understood correctly, the OP needs to differentiate 22.5 from 22.50. In your table, 2.1 is not the same as 2.10, 2 is not the same as 2.0. This is about evaluating that the student writes the observed value in the measuring instrument according to the precision of the instrument. In an advanced course, this topic corresponds to metrology. Oct 11, 2023 at 2:21
  • 1
    This is one of the challenges of digital transformation in schools when passing from paper to digital using a tool like spreadsheets because it's handy, free, etc. :) Oct 11, 2023 at 2:29
  • 1
    All other things being equal, I would be inclined to agree, but in this case I don't think so. The OP wants to make sure the students record the tenth's and the OP controls the test parameters so can ensure their problem returns a non-zero number in that position. I think that's the path of least resistance. The alternative is not practical, relies on a lot of effort on the part of each and every student and is susceptible to typos. Be interesting to get clarification from OP.
    – Blindspots
    Oct 11, 2023 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.