topic How to create a column that counts the total specified values form other columns? in Discussions

How to create a column that counts the total specified values form other columns?

lehoth — Sat, 10 Jun 2023 23:35:33 GMT

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.

Re: How to create a column that counts the total specified values form other columns?

txnelson — Mon, 16 Aug 2021 07:19:27 GMT

Here is one way to create the formula for this

Sum( :student 1 == :right answer, :student 2 == :right answer )

Re: How to create a column that counts the total specified values form other columns?

jthi — Mon, 16 Aug 2021 07:25:31 GMT

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):

Re: How to create a column that counts the total specified values form other columns?

lehoth — Mon, 16 Aug 2021 13:59:06 GMT

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)

Re: How to create a column that counts the total specified values form other columns?

jthi — Mon, 16 Aug 2021 14:56:35 GMT

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))
	)
);

Re: How to create a column that counts the total specified values form other columns?

lehoth — Mon, 16 Aug 2021 16:17:03 GMT

thank you very much, Mr.Jthi ^^