Short answer
Instead of using the Insert > Checkbox use the Unicode characters ballot box / checked ballot box:
or another similar character / emoji
Example
=IF(ISTEXT(A1),"☑","☐")
Explanation
At this time there isn't an operand, function or format for an Insert > Checkbox the same as there isn't any of this for an Insert > Chart, Insert > Image, Insert > Note, Insert > Comment
NOTES:
- SPARKLINE function works completely different than an Insert > Chart
- IMAGE function works completely different than an Insert > Image
The exception could be:
- HYPERLINK function similar to an Insert > Link
First, you need to decide how you enter your duration values. Remember, 1,30 minutes
is not the same as 1 minute and 30 seconds
- it is 1 minute and 18 seconds
, since a minute is 60 seconds.
If you insist on a format of minutes,seconds
, you'll need a formula to parse this as a duration. I suggest using a regular expression. This formula will split a minutes,seconds
value in A2
into two cells B2
and C2
:
=REGEXEXTRACT(A2, "(\d+),(\d+)")
Having that, we can calculate a decimal number of minutes (so that 1 minute and 30 seconds
is displayed as 1.5
minutes). Enter this formula in D2
:
=B2+C2/60
Now that we know that 1 minute, 30 seconds
equals 1.5 minutes
, it is easy to calculate the charge. Let's put the minute charge in cell G1
, then, in C2
, enter:
=D2*$G$1
... which should display as €0.15
.
See the example spreadsheet I have set up.
Note: Instead of parsing a minutes,seconds
string with regex, you might have some luck experimenting with Format → Number → More formats, and using the Elapsed minutes/Elapsed seconds placeholders, but the feature seems buggy and non-obvious.
Best Answer
This can't be done via "standard" means. Formulas are not permitted as custom checked/unchecked values.
here is one of many methods of how to fake it:
IF
statement to insert those images based on another checkbox