Calculate Percentage of True (or False) in a range
 

Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.

Answer: Calculate Percentage of True (or False) in a range
 

Yes, you can use the COUNTIF function in Excel to calculate the percentage of "True" values in a range. Here are the steps to do so:

1. Select the range of cells that contains the "True" and "False" values.
2. Use the
   Code:

   ---

   COUNTIF

   ---

   function to count the number of "True" values in the range. The formula would look something like this:
   Code:

   ---

   =COUNTIF(A1:A10,"True")

   ---

3. Subtract the number of "True" values from the total number of cells in the range to get the number of "False" values. The formula would look something like this:
   Code:

   ---

   =COUNTA(A1:A10)-COUNTIF(A1:A10,"True")

   ---

4. Divide the number of "True" values by the total number of non-weekend days in the range to get the percentage of "True" values. You can use the
   Code:

   ---

   NETWORKDAYS

   ---

   function to exclude weekends from the count. The formula would look something like this:
   Code:

   ---

   =COUNTIF(A1:A10,"True")/(NETWORKDAYS(start_date,end_date)-number_of_weekends)

   ---

You can then copy and paste this formula to automatically update the percentage of "True" values each day as you update the True/False values for the previous day.

Answer: Calculate Percentage of True (or False) in a range
 

Yes, you can use the COUNTIF function in Excel to calculate the percentage of "True" values in a range. Here are the steps to do so:

1. First, select the range of cells that contain the "True" or "False" values.
2. Next, use the
   Code:

   ---

   COUNTIF

   ---

   function to count the number of "True" values in the range. The formula for this would be:
   Code:

   ---

   =COUNTIF(range,"True")

   ---

3. To calculate the percentage of "True" values, divide the count of "True" values by the total number of values in the range. The formula for this would be:
   Code:

   ---

   =COUNTIF(range,"True")/COUNTA(range)

   ---

4. To exclude weekends (Saturday and Sunday) from the calculation, you can use the
   Code:

   ---

   WEEKDAY

   ---

   function to check if the date falls on a weekend. The formula for this would be:
   Code:

   ---

   =IF(WEEKDAY(date,2)<6,COUNTIF(range,"True")/COUNTA(range),"")

   ---

5. Finally, to automatically update the percentage each day, you can use the
   Code:

   ---

   TODAY

   ---

   function to reference the current date. The formula for this would be:
   Code:

   ---

   =IF(WEEKDAY(date,2)<6,COUNTIF(range,"True")/COUNTA(range),"")

   ---

By using these formulas, you can calculate the percentage of "True" values in a range, exclude weekends, and automatically update the percentage each day as you update the "True" or "False" values for the previous day.

Calculate Percentage of True (or False) in a range
 

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Graham" wrote in message
...
Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.

Calculate Percentage of True (or False) in a range
 

In , Bernard Liengme
spake thusly:

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)

That of course works fine, but it seems like overkill to me
given that a sheet full of SUMPRODUCT, etc., formulas will
get top-heavy fast (suck away resources).

What about just:

=COUNTIF(A1:A100,"True")/COUNTA(A1:A100)

--
dman

Calculate Percentage of True (or False) in a range
 

Dear Bernard, Many thanks for your help!
I'm getting an error #NAME? with this formula:
=SUMPRODUCT(R35:R55=Yes)/(COUNTA(R35:R55))
Are you able to see where I'm going wrong?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Graham" wrote in message
...
Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.

Calculate Percentage of True (or False) in a range
 

Why have you YES in your formula rather than TRUE?

If the cells contain "Yes" then it needs to be in quotes:

=SUMPRODUCT(--(R35:R55="Yes")/(COUNTA(R35:R55))

OR

see the alternative reply:

=COUNTIF(R33:R55,"Yes")/COUNTA(R35:R55)


"Graham" wrote:

Dear Bernard, Many thanks for your help!
I'm getting an error #NAME? with this formula:
=SUMPRODUCT(R35:R55=Yes)/(COUNTA(R35:R55))
Are you able to see where I'm going wrong?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Graham" wrote in message
...
Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.

Calculate Percentage of True (or False) in a range
 

Could be:

=SUMPRODUCT(R35:R55="Yes")/(COUNTA(R35:R55))



Graham wrote:

Dear Bernard, Many thanks for your help!
I'm getting an error #NAME? with this formula:
=SUMPRODUCT(R35:R55=Yes)/(COUNTA(R35:R55))
Are you able to see where I'm going wrong?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Graham" wrote in message
...
Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.




--

Dave Peterson

Calculate Percentage of True (or False) in a range
 

Thank you all for your help, I have now got it to work over a specific range.
Can I now modify it so that the formula will automatically include a new
bottom row, identified by the days date, each time I update the spreadsheet ?
i.e. tomorrow I will want it to include R57? Currently reads:

=COUNTIF(R36:R56,"Yes")/COUNTA(R36:R56)*100

Many Thanks


"Toppers" wrote:

Why have you YES in your formula rather than TRUE?

If the cells contain "Yes" then it needs to be in quotes:

=SUMPRODUCT(--(R35:R55="Yes")/(COUNTA(R35:R55))

OR

see the alternative reply:

=COUNTIF(R33:R55,"Yes")/COUNTA(R35:R55)


"Graham" wrote:

Dear Bernard, Many thanks for your help!
I'm getting an error #NAME? with this formula:
=SUMPRODUCT(R35:R55=Yes)/(COUNTA(R35:R55))
Are you able to see where I'm going wrong?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Graham" wrote in message
...
Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.

Calculate Percentage of True (or False) in a range
 

Make the range a dynamic range.

If you aren't familiar with dynamic ranges, take a look he

http://www.contextures.com/xlNames01.html#Dynamic

"Graham" wrote:

Thank you all for your help, I have now got it to work over a specific range.
Can I now modify it so that the formula will automatically include a new
bottom row, identified by the days date, each time I update the spreadsheet ?
i.e. tomorrow I will want it to include R57? Currently reads:

=COUNTIF(R36:R56,"Yes")/COUNTA(R36:R56)*100

Many Thanks


"Toppers" wrote:

Why have you YES in your formula rather than TRUE?

If the cells contain "Yes" then it needs to be in quotes:

=SUMPRODUCT(--(R35:R55="Yes")/(COUNTA(R35:R55))

OR

see the alternative reply:

=COUNTIF(R33:R55,"Yes")/COUNTA(R35:R55)


"Graham" wrote:

Dear Bernard, Many thanks for your help!
I'm getting an error #NAME? with this formula:
=SUMPRODUCT(R35:R55=Yes)/(COUNTA(R35:R55))
Are you able to see where I'm going wrong?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Graham" wrote in message
...
Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.

Calculate Percentage of True (or False) in a range
 

Thanks once again. No, I've not come across dynamic ranges before, will try
and read up a bit first, but may have to come back to you on this!

"Toppers" wrote:

Make the range a dynamic range.

If you aren't familiar with dynamic ranges, take a look he

http://www.contextures.com/xlNames01.html#Dynamic

"Graham" wrote:

Thank you all for your help, I have now got it to work over a specific range.
Can I now modify it so that the formula will automatically include a new
bottom row, identified by the days date, each time I update the spreadsheet ?
i.e. tomorrow I will want it to include R57? Currently reads:

=COUNTIF(R36:R56,"Yes")/COUNTA(R36:R56)*100

Many Thanks


"Toppers" wrote:

Why have you YES in your formula rather than TRUE?

If the cells contain "Yes" then it needs to be in quotes:

=SUMPRODUCT(--(R35:R55="Yes")/(COUNTA(R35:R55))

OR

see the alternative reply:

=COUNTIF(R33:R55,"Yes")/COUNTA(R35:R55)


"Graham" wrote:

Dear Bernard, Many thanks for your help!
I'm getting an error #NAME? with this formula:
=SUMPRODUCT(R35:R55=Yes)/(COUNTA(R35:R55))
Are you able to see where I'm going wrong?

"Bernard Liengme" wrote:

=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Graham" wrote in message
...
Hi, I have a column of values, either "true" or "false".
The rows are current dates. I want to calculate the % of "True" from a
specified row (prior date) to "Yesterdays" date. (Saturday & Sundays are
excluded!)
Can I use one of the standard functions to automatically calculate the %
"true", automatically updated each day, as I update the True/False for the
previous day?
Any pointers would be much appreciated.