Measure column - summarization

[dart73]

Hello guys,






I have a matrix with some measure columns that creates some conditions. That´s the Delta_classification.






Delta_classification = if([DL_TPH_Delta]>0,5;"CRITICAL";if([DL_TPH_Delta]>0,3;"BAD";if([DL_TPH_Delta]>0,2;"REGULAR";if([DL_TPH_Delta]>0,1;"ACCEPTABLE";if([DL_TPH_Delta]>0,05;"GOOD";"EXCELLENT")))))


Now I want to summarize it by category CRITICAL, BAD, REGULAR, ACCEPTABLE, GOOD, EXCELLENT but as this is a measure I"m having trouble to do it. Could you kindly help me on this?!






BR

 

[Col Delane]

Hi BR and welcome to the Forum

Firstly, sorry about how this looks - the result of dodgy copying & pasting from Excel!!!

Secondly, nesting so many IF functions together to return the appropriate classification is not good practice. A better option is a central lookup table like the following:

<tbody>
[TD="class: xl66"]

Excelent
Good
Regular
Bad
Critical
Excelent
Good
Acceptable
Regular
Acceptable
Regular
Bad
Critical
Good
Regular

<tbody>
[TD="class: xl71"]A[/TD]
[TD="class: xl71"]B[/TD]
[TD="class: xl71, width: 230"]C[/TD]

[TD="class: xl71"]2[/TD]
[TD="class: xl73"]Table Name: tblDeltaClass[/TD]
[TD="class: xl72"][/TD]

[TD="class: xl71"]3[/TD]
[TD="class: xl67, width: 230"]DL TPH Delta[/TD]
[TD="class: xl68"]Classification[/TD]

[TD="class: xl71"]4[/TD]
[TD="class: xl69"]-[/TD]

[TD="class: xl71"]5[/TD]
[TD="class: xl69"]0.05[/TD]

[TD="class: xl71"]6[/TD]
[TD="class: xl69"]0.10[/TD]
[TD="class: xl65"]Acceptable[/TD]

[TD="class: xl71"]7[/TD]
[TD="class: xl69"]0.20[/TD]

[TD="class: xl71"]8[/TD]
[TD="class: xl69"]0.30[/TD]

[TD="class: xl71"]9[/TD]
[TD="class: xl69"]0.50[/TD]

[TD="class: xl71"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]

[TD="class: xl71"][/TD]

[TD="class: xl71"][/TD]
[TD="class: xl73"]Table Name: tblData[/TD]

[TD="class: xl71"][/TD]
[TD="class: xl65, colspan: 2"]C15 = VLOOKUP( B15, tblDeltaClass, 2, TRUE )[/TD]

[TD="class: xl71"]14[/TD]
[TD="class: xl67, width: 230"]ID[/TD]
[TD="class: xl66, width: 230"]DL TPH Delta[/TD]

[TD="class: xl71"]15[/TD]
[TD="class: xl69"]0.02[/TD]

[TD="class: xl71"]16[/TD]
[TD="class: xl69"]0.05[/TD]

[TD="class: xl71"]17[/TD]
[TD="class: xl69"]0.10[/TD]

[TD="class: xl71"]18[/TD]
[TD="class: xl69"]0.20[/TD]

[TD="class: xl71"]19[/TD]
[TD="class: xl69"]0.14[/TD]

[TD="class: xl71"]20[/TD]
[TD="class: xl69"]0.24[/TD]

[TD="class: xl71"]21[/TD]
[TD="class: xl69"]0.34[/TD]

[TD="class: xl71"]22[/TD]
[TD="class: xl69"]0.54[/TD]

[TD="class: xl71"]23[/TD]
[TD="class: xl69"]0.08[/TD]

[TD="class: xl71"]24[/TD]
[TD="class: xl69"]0.22[/TD]

</tbody>

[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl68"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl72, colspan: 2"][/TD]

</tbody>

Now, what do you mean by "summarise it by category"?

To COUNT the number of entries by category, set up another table like this:

<tbody>
[TD="class: xl70"][/TD]
[TD="class: xl69"]Table Name: tblSummary[/TD]
[TD="width: 230"][/TD]

[TD="class: xl70"][/TD]
[TD="class: xl66, colspan: 2"]C30 = COUNTIFS( $C$15:$C$24, B30 )[/TD]

[TD="class: xl70"]29[/TD]
[TD="class: xl67"]Classification[/TD]
[TD="class: xl65"]Count[/TD]

[TD="class: xl70"]30[/TD]
[TD="class: xl68"]Excelent[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl70"]31[/TD]
[TD="class: xl68"]Good[/TD]
[TD="class: xl66"]2[/TD]

[TD="class: xl70"]32[/TD]
[TD="class: xl68"]Acceptable[/TD]
[TD="class: xl66"]2[/TD]

[TD="class: xl70"]33[/TD]
[TD="class: xl68"]Regular[/TD]
[TD="class: xl66"]3[/TD]

[TD="class: xl70"]34[/TD]
[TD="class: xl68"]Bad[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl70"]35[/TD]
[TD="class: xl68"]Critical[/TD]
[TD="class: xl66"]1[/TD]

[TD="class: xl70"]36[/TD]
[TD="class: xl71"] Total [/TD]
[TD="class: xl72"] 10 [/TD]

</tbody>

Alternatively, if your data has a much greater number of entries, the better option may be to create a Pivot Table where you can choose any one of several measures, or perform full statistical analysis (standard deviations, etc.).