Last active
February 19, 2025 10:22
-
-
Save zaskem/d36636b3cf3bc3e061d5b127e4f897e5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Column A | Column B | Column C | Formula | |
---|---|---|---|---|
1 | 2 | 5 | =SUM(A2:C2) | |
5 | 3 | 10 | =A3*C3+B3 | |
2 | 2 | 4 | =COUNTIF(A:C,2) | |
0 | 0 | No | =IF(and(A5=0,B5=0,C5="Yes"),1,0) | |
0 | 0 | Yes | =IF(and(A6=0,B6=0,C6="Yes"),1,0) | |
1 | 0 | 1 | =IF(AND(A7=1,B7=0),"UPDATE",IF(AND(A7=0,B7=1),"CHECK","No Change")) | |
0 | 1 | 1 | =IF(AND(A8=1,B8=0),"UPDATE",IF(AND(A8=0,B8=1),"CHECK","No Change")) | |
0 | 0 | 0 | =IF(AND(A9=1,B9=0),"UPDATE",IF(AND(A9=0,B9=1),"CHECK","No Change")) |
That's a great find -- thanks for sharing!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Just came across your related blog post, and it saved my day! I'm working against ("with" didn't seem quite appropriate here 😉 ) Apple's Numbers spreadsheet app, and I thought I'd share my observations. Perhaps it would work with M$'s Excel, too?
The
,
(comma) appears to be a pivotal element, since it seems it is what prompted you to embark on the "double-quoting scary ride"? I managed to jump on a slightly less scary ride, by using;
(semi-colon) as the field separator. This allows the,
(comma) to be used as the separator of function arguments, without the danger of being mistaken as the field separator. As a consequence, in Apple Numbers the formulas can be used without being surrounded by quotes, and thereby also removing the need for double-double-quoting verbatim strings in function arguments. The following, when imported as a CSV file into Apple Numbers, reads as fomrtulas as expected, and causes the expected calculations to be performed:I also tried pinning row and column specs (i.e. e.g.
$B$9
instead ofB9
) in formulas, which worked as expected, too.With Excel, as always, your mileage may vary however.