Calculation in Matrix

[Anonymous]

Hello All,

Can anyone please give me an idea how I can acheive the below result. 

I have columns Person ID, Grade (B,C,D,E,F etc) and Rating (2 levels, 1 levels, Develop). 

Short is a additional calculated column in the table. If I use matrix then I will get result for 2 levels, 1 levels and Develop but for Short the calculation is tricky as it uses data from different grades.

Below is the formula for Short in Grade B : sum(2 level and 1 level from grade C)/ Total for grade B  i.e (6+1)/10. 

Similarily for Short in Grade C : (4+2)/8

	B	C	D	E	F
Total	10	8	10	4	2
2 levels	6	6	4	1	0
1 levels	2	1	2	0	1
Develop	2	1	4	3	1
Short	0.7	0.75	0.1	0.25

Please let me know if I am not clear.

[prateekraina]

Hi @Anonymous,

These grade columns which you mentioned, Are they Fixed or are they dynamically changing?

In other words, can there be, depending on source data, only two grades say B & C and sometimes they can be 4 say B,C,D & E and other random cases?

Prateek Raina

[Anonymous]

Hi Prateek,

They are dynamic, means the data in source might be only for B and C grades and sometimes there might be data for all grades.

Thank you.

[prateekraina]

Hi @Anonymous,

And is there any relation between their names or any kind of pattern so that we can identify which is the next column.

For example: In your dummy data we can assume that C comes after B so we calculate short of B based on C values etc.

Prateek Raina

[Anonymous]

Yes there is sorting on grade so the sequence will be B,C,D,E,F and its fixed.

[Anonymous]

Hi All,

Still havent found a solution for this, can anyone please provide your ideas to acheive this.

Thanks

[Anonymous]

Hi @Anonymous,

It is hard to write a formula to loop text values, you need to add condition to check current grade and which grade need to be calculated.(for text value, it not contains big than, less than or other math operations, it only has equal, not equal, contains operations)

Regards,

Xiaoxin Sheng