Excel formula help

Question

I have column (A) with 100 rows that have a (0), or a (1), or a (2). I have 3 cells that =COUNTIF(A1:A100,"0") 0r "1" or "2" respectfully for a total of each at the bottom. How do I get a cell formula to let me know which number is represented as a percentage of the current total. i.e. if there are 60 (1's), 30 (2's), and 10 (0's) I want it to express each one as a percentage of the total that is currently entered not as a percentage of 100 rows.

Answer 1

If a cell contains a time before 7:00, subtracting 7 hours would result in a negative time. Excel cannot handle that.

You could use

=MOD($C$3:$C$102-7/24,1)

Answer 2

Thank you very much.

Sorry to ask you another question, but I had a formula that was working fine until I hit a snag. The formula was to take and convert a GMT time frame into an MST time frame. The formula is copied into cells B3:B102 and is as follows; =$C$3:$C$102-7/24.  The formula seems to work for all times from 7:00 - 0:00 but it doesn't seem to work for anything from between 1:00 - 6:00. Do you by any chance know why or what I can do to fix this?

Answer 3

Hi,

try below formula

=TEXT(SUMPRODUCT(($A$2:$A$25=0)*($A$2:$A$25<>""))/COUNTIF($A$2:$A$25,"<>"&""),"0%")

=TEXT(SUMPRODUCT(($A$2:$A$25=1)*($A$2:$A$25<>""))/COUNTIF($A$2:$A$25,"<>"&""),"0%")

=TEXT(SUMPRODUCT(($A$2:$A$25=0)*($A$2:$A$25<>""))/COUNTIF($A$2:$A$25,"<>"&""),"0%")

Regards

Answer 4

=COUNTIF(A1:A100,0)/COUNT(A1:A100)

=COUNTIF(A1:A100,1)/COUNT(A1:A100)

=COUNTIF(A1:A100,2)/COUNT(A1:A100)