Creating a new column to get total

[Dheeru28]

Hi

I have the following data:

	Total Week	Mon	Tue	Wed	Thu	Fri
A		5	3	12	1	2
B		2	4	10	4	7
B/A		0.4	1.333333	0.833333	4	3.5
(A+B)		7	7	22	5	9
C		2	9	3	8	0
C/B		1	2.25	0.3	2	0
C/(A+B)		0.285714	1.285714	0.136364	1.6	0

What I need is the output for the total week column. While we can use formula for adding rows A, B and C for Mon-Fri, that won't give correct output for B/A, (A+B), etc.

Can someone please suggest a way to solve this?

Thanks a lot!! 

[Christina_H · Accepted solution]

You need to complete the other calculations after summing A, B and C, something like this

[Dheeru28]

Thank you very much @Christina_H 

The RegEx match formula you have used in the filter tool gives 0 records on my data. It's likely because in my data, "\w+" is not working.

The rows that need to be filtered out (in my data) have "%' in them. How do we remove them?

[Christina_H]

Much simpler if they all contain %.

!Contains([Calc],"%")

You might also need to correct the names before joining back to the original data - crosstab replaces spaces and punctuation with _

[Dheeru28]

Thanks a lot, it worked.

[Dheeru28]

@Christina_H

A follow up question: If I also want to do the same calculations for, say, Mon+Tue and Thu+Fri in new columns, will I need to repeat this process for each of the new columns or is there a way to do them all together?

[Christina_H · Accepted solution]

You can do it all at once with some small changes to the process, see attached.  If you also needed e.g. Wed+Thu you'd need to add a step to duplicate columns in multiple groups.

[Dheeru28]

Thank you so much! This will make my workflow a lot less crowded.