As long as I have been involved with computers, I have avoided spreadsheets. This has finally caught with me. :smack:
I’ve essentially got this situation, using Google Spreadsheets, which seems to have almost all of the same functions as Open Office Calc, which was modeled off of MS Excel:
| x | y | z ...
-----------------
a | ✓+ | ✓+ | ✓-
-----------------
b | ✓ | ✓- | ✓+
-----------------
c | ✓+ | ✓ | ✓
-----------------
...
I’d like the checks to be equivalent to a 3, 4 and 5 respectively, and be able to do general statistics (mean median mode sd, etc…) on individual rows, individual columns, the set of rows and the set of columns. This seems to be well documented, but I still haven’t figured out a way to deal with the check marks, which are a necessary constraint. Does anyone know how to deal with this?
If i understand you correctly, you want the spreadsheet to interpret a ✓- as the value 3, a ✓ as the value 4, and a ✓+ as the value 5. And you then want to have the spreadsheet perform calculations on the checkmarks, based upon those values. Is that correct?
If so, then the way to do it (at least in Excel), is using the VLOOKUP function. This essentially allows you to create a table of values, where:
✓- = 3
✓ = 4
✓+ = 5
and then tell the spreadsheet to use these values in making calculations.
Now, i’m not an expert at this stuff. In fact, i only learned about it a couple of months ago, by asking on these boards. I wanted a system that would assign numerical values to letter grades for my classes. Here is the thread.
A site that was very helpful to me, especially in working out the VLOOKUP function, was this page. It discusses precisely the issues i needed, and the guy also has a sample spreadsheet that you can download and copy the code from.
I assume that non-MS spreadsheets like OpenOffice Calc and Google Spreadsheets have a VLOOKUP equivalent.
