Percentage Question!

[Helpmepleasehahaha]

Howdy all!

So I am trying to figure out how to make this idea a reality. I'm not even sure that I can but I figured I would ask the web of experts and see instead of screaming silently in my head over and over.

Idea: I want to be able to calculate the percentage of vehicles in my squadron that are Fully Mission Capable (Green). We have 48 vehicles and this tracker is linked to a PowerPoint slide utilized by many other organizations for briefings and what not. The cell Q2 "FLEET MC RATE:" is where I want to reflect the percentage. There are 5 colors on this tracker but the percentage is only for items that are green. Now, every vehicle box is conditioned to reflect a color based on the letter code typed into the box. Example, if I want to show a vehicle 01E00036 in cell D8, Fully Mission Capable (Green), I type the letter "a" in it and it conditions it green. I really hope I explained this well. Any and all help is welcome

 

[6StringJazzer]

The way you would do this is by counting the number of "a" cells, and then the number of the all other coded cells, and doing a division. (There is no simple way to count by color.) Your picture doesn't give insight as to what this looks like in Excel but you might have something like this:

=COUNTIF(A1:J20,"a")/(COUNTIF(A1:J20,"?")

This counts all the cells in the range that have just the letter "a" in the cell and divides by the number of cells in the same range that have a single character in the cell. This will work if the only cells with a single character are the ones coded for vehicle status. Otherwise we will have to list each possible code.

 

[leatherhen99]

Hiya! I can't tell where the cells are because I don't see the row and column headers. What percentage are you trying to get? The percent of all 48 vehicles that are Fully Mission Capable, or are you looking for a percentage based on the column or the row?

 

[Helpmepleasehahaha]

Howdy all!

So I am trying to figure out how to make this idea a reality. I'm not even sure that I can but I figured I would ask the web of experts and see instead of screaming silently in my head over and over.

Idea: I want to be able to calculate the percentage of vehicles in my squadron that are Fully Mission Capable (Green). We have 48 vehicles and this tracker is linked to a PowerPoint slide utilized by many other organizations for briefings and what not. The cell Q2 "FLEET MC RATE:" is where I want to reflect the percentage. There are 5 colors on this tracker but the percentage is only for items that are green. Now, every vehicle box is conditioned to reflect a color based on the letter code typed into the box. Example, if I want to show a vehicle 01E00036 in cell D8, Fully Mission Capable (Green), I type the letter "a" in it and it conditions it green. I really hope I explained this well. Any and all help is welcome

 

[6StringJazzer]

Based on your second picture this is the actual formula. This assumes that that only cells that contain a single letter are the coded cells, and also that all coded cells contain a single letter.

=COUNTIF(B8:W28,"a")/(COUNTIF(B8:W28,"?")