It's easy to do this one in Excel with the "countif" function but, How can define a column that counts the total specified values from other columns?
example: I have a table of the total answers, how can I count the right answer for each question?
| question | student 1 | student 2 | right answer | total answer |
| 1 | A | A | A | 2 |
| 2 | A | B | B | 1 |
| 3 | C | B | A | 0 |
thank you very much.
Let's start with this table (total_answers is there just to show correct amount):
1. Stack
2. Add formula (Column 6):
3. Tabulate (or summary):
Also take care that the column Data Types and Modeling Types are correct for the data you are modeling.
Example table with a bit more advanced formula which I wouldn't suggest using until you learn a bit more about JSL:
Names Default To Here(1);
dt = New Table("Untitled 4",
Add Rows(5),
Set Header Height(50),
New Column("question", Numeric, "Ordinal", Format("Best", 12), Set Values([1, 2, 3, 4, 5])),
New Column("student1", Character, "Nominal", Set Values({"A", "A", "A", "A", "A"})),
New Column("student2", Character, "Nominal", Set Values({"A", "B", "B", "B", "A"})),
New Column("student3", Character, "Nominal", Set Values({"A", "B", "C", "C", "A"})),
New Column("student4", Character, "Nominal", Set Values({"A", "B", "C", "D", "A"})),
New Column("student5", Character, "Nominal", Set Values({"A", "B", "C", "D", "A"})),
New Column("right_answer", Character, "Nominal", Set Values({"A", "B", "C", "D", "E"}))
);
//Don' use if you don't have clear understanding what this does! Try first using data table method
//2::5 indicates indices of student result columns
dt << New Column("Total_answers", Numeric, Continuous,
<< Formula(
N Items(Loc(Current Data Table()[Row(), 2::6], :right_answer))
)
);
what if I have many students, I tried to do a for loop based on your suggestion, but I couldn't get it.
(I also collect in other classes)
Many was to do this in JMP but they do differ from excel because JMP isn't a spreadsheet.
Some examples with formulas:
N Items(Loc(Words(:"student 1"n || :"student 2"n, ""), :"right answer"n));
N Items(Loc(Current Data Table()[row(), {"student 1", "student 2"}], :"right answer"n))
Datatables: Stack table with student columns:
Add formula to check differences between answer and and right answer:
Formula for Column 5:
If(:right answer == :Data,
1,
0
)
Create tabulate/summary (you might have to change the modeling type of question to Nominal or Ordinal):
Here is one way to create the formula for this
Sum( :student 1 == :right answer, :student 2 == :right answer )
