Excel and Percentage Calculation

Hi there,

I don't know if I am posting at the right thread but anyways, here is my question.

I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out.

Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?

Any help would be appreciated. Thanks.

Hi, and welcome to the forum.

The COUNTA() function counts the number of non blank cells in a range. So assuming say column A contains your list of devices, starting in say A10, and column B is where you enter your "X"s, use the following function:

=COUNTA(A10:A1010)/COUNTA(B10:B1010)

adjust the ranges as necessary.

HTH

If I understand correctly all you need is

=counta(cell range)/100

For my example, I put X's in cells a1:a100 so my formula was

=counta(A1:A100)/100

then format the cell as a %

Thanks a lot guys, I will try this one out.

Hi,

That's fine if you know in advance how many records there are - 100 in this example. However the OP mentioned 'lots of data' and >1000 rows. This implies the divisor is a variable number, hence it needs to be worked out.

Rgds

Well thats why your the moderator and I'm not :-)

Excel Experts and Gurus,

A follow-up on the above topic.

Is there a way, that I could format the fonts on each counted % for each cell? For instance, if the total percentage for one cell has a value of 75%, Is there a way, that we could make a format or formula that would make the value of 75% turn to bold red and if there are cells that has a value of 100% Green as well? This way, it tells us what devices needs to be completed. Just like what I have below:

% Run Commission for Device A (Cell A1 - should have bold red font) - 75% (Cell B1 should have bold red font)

If the cell is less than 100%, then the font would be bold and red and if the cell has 100%, the font would be bold green.

Any help would be highly appreciated. Thanks a lot.

Kind regards,
Servie

Hi,

Yes, simplest way is to use Conditional Formats.

HTH

Hi Richard,

The conditional format worked for my percentage data on one column. But I can't seem to figure out how make the cell beside the percentage have the same color though.

For Instance:

Column 1: % Completed Devices - Should be red
Column 2: 75% - Should be red

Based on the above, I was able to do a Conditional Format for Column 2 but could not figure out how to make a Conditional Format for Column 1 so that if the value is 99% or lesser the fonts would turn red and if the value is 100%, the font would turn blue. In other words, if Column 2 is Red, so should Column 1.

Any thoughts on this would be highly appreciated. Thank you very much.

Hi,

Instead of using the 'Cell Value is" option within Cond. Formatting, use the 'Formula Is' option. i.e.

in A1 use the first CF and enter

=B1<100% and set the red format

and set the second CF

=B1>=100% and set the blue format.

HTH

Hi Richard,

Thanks very much for the reply.

It works!

Cell A1 turns Font to Green if Cell B1 has 100% And Cell A1 turns Font to Red if Cell B1 has less than 100%.

This is great. However, I would like to copy this to the succeeding cells below which goes all the way down to 1000+ rows which is a lot.

When I do this, it copies the Format but not the functionality of the CF based on the other Cells (B2, B3, B4, B5....) data.

Any help that would putting conditional formats for all cells concerned to make my job faster would be highly appreciated. Again, thanks a lot Richard.

Hi,
Are you also using the Edit Copy PasteSpecial Formats? It sounds like you've just copied the cell values/formulae.

Copy A1, or the cell where you've entered the CF, then select the whole of the column and do the Paste Special Formats

Rgds