Finding the correct % value

[colin5392]

I have 20 assets (devices)

9 of the devices have vulnerabilities. Device 1 has 8 x critical vulnerabilities and 20 x High vulnerabilities.

How do I correctly determine the true percentage values of Critical vulnerabilities and High vulnerabilities against number of assets with issues identified and the total number of devices?

Thanks, in advance.

 

[tyija1995]

Hey is this something you're after?

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Device[/TD]
[TD]Crit V[/TD]
[TD]High V[/TD]
[TD]Count V[/TD]
[TD]Crit V %[/TD]
[TD]High V %[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Device 1[/TD]
[TD]8[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]28.6%[/TD]
[TD]71.4%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Device 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Device 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Device 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Device 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Device 6[/TD]
[TD]5[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]27.8%[/TD]
[TD]72.2%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Device 7[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Device 8[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]30.0%[/TD]
[TD]70.0%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Device 9[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Device 10[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]22.2%[/TD]
[TD]77.8%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Device 11[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Device 12[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]41.2%[/TD]
[TD]58.8%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Device 13[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Device 14[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]50.0%[/TD]
[TD]50.0%[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Device 15[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]62.5%[/TD]
[TD]37.5%[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Device 16[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]12.5%[/TD]
[TD]87.5%[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Device 17[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Device 18[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]100.0%[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Device 19[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Device 20[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Columns B & C i populated with random data,

Column D (D2) holds the formula which can be copied down the D column: IF(COUNT(B2:C2)>0,1,0)

With D22 as: SUM(D2:D21)/COUNT(D2:D21)

Columns E (E2) holds the formula which can be copied down the E AND F column: IF($D2=1,B2/(SUM($B2:$C2)),"")

 

[colin5392]

Hey is this something you're after?

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Device[/TD]
[TD]Crit V[/TD]
[TD]High V[/TD]
[TD]Count V[/TD]
[TD]Crit V %[/TD]
[TD]High V %[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Device 1[/TD]
[TD]8[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]28.6%[/TD]
[TD]71.4%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Device 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Device 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Device 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Device 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Device 6[/TD]
[TD]5[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]27.8%[/TD]
[TD]72.2%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Device 7[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Device 8[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]30.0%[/TD]
[TD]70.0%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Device 9[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Device 10[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]22.2%[/TD]
[TD]77.8%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Device 11[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Device 12[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]41.2%[/TD]
[TD]58.8%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Device 13[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Device 14[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]50.0%[/TD]
[TD]50.0%[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Device 15[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]62.5%[/TD]
[TD]37.5%[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Device 16[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]12.5%[/TD]
[TD]87.5%[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Device 17[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Device 18[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]100.0%[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Device 19[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Device 20[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Columns B & C i populated with random data,

Column D (D2) holds the formula which can be copied down the D column: IF(COUNT(B2:C2)>0,1,0)

With D22 as: SUM(D2:D21)/COUNT(D2:D21)

Columns E (E2) holds the formula which can be copied down the E AND F column: IF($D2=1,B2/(SUM($B2:$C2)),"")

I'll give it a go - thank you for your assistance