Calculate Percentage of True (or False) in a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Bernard Liengme said:
=SUMPRODUCT(--(A1:A100=TRUE))/COUNTA(A1:A100)
Click to expand...

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)
 
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?
 
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)
 
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
 
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!
 
Back
Top