Finding the correct % value

colin5392

Board Regular
Joined
Oct 25, 2016
Messages
67
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hey is this something you're after?

ABCDEF
1DeviceCrit VHigh VCount VCrit V %High V %
2Device 1820128.6%71.4%
3Device 20
4Device 30
5Device 40
6Device 50
7Device 6513127.8%72.2%
8Device 70
9Device 837130.0%70.0%
10Device 90
11Device 1027122.2%77.8%
12Device 110
13Device 12710141.2%58.8%
14Device 130
15Device 1488150.0%50.0%
16Device 1553162.5%37.5%
17Device 1617112.5%87.5%
18Device 170
19Device 1831100.0%0.0%
20Device 190
21Device 200
2245%

<tbody>
</tbody>


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)),"")
 
Upvote 0
Hey is this something you're after?

ABCDEF
1DeviceCrit VHigh VCount VCrit V %High V %
2Device 1820128.6%71.4%
3Device 20
4Device 30
5Device 40
6Device 50
7Device 6513127.8%72.2%
8Device 70
9Device 837130.0%70.0%
10Device 90
11Device 1027122.2%77.8%
12Device 110
13Device 12710141.2%58.8%
14Device 130
15Device 1488150.0%50.0%
16Device 1553162.5%37.5%
17Device 1617112.5%87.5%
18Device 170
19Device 1831100.0%0.0%
20Device 190
21Device 200
2245%

<tbody>
</tbody>


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
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top