Calculate Percentage of True (or False) in a range

G

Graham

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

Dallman Ross

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)
 
G

Graham

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?
 
T

Toppers

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)
 
G

Graham

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
 
G

Graham

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!
 
Top