Finding the correct % value

colin5392

Board Regular
Joined
Oct 25, 2016
Messages
56
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
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)),"")
 

colin5392

Board Regular
Joined
Oct 25, 2016
Messages
56
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,317
Messages
5,635,527
Members
416,862
Latest member
MGDlite

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
Top