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.

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.

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.

=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.

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

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.

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.

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

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.

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.

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.