If, then statements

[Guest]

Hi all, thanks for your help! I have two columns of 10 cells each that will
have percentages in them (already calculated). My results cell needs to say
"yes" if 90% or greater of these cells (18 out of 20) show >=50%, "no" if 90%
of the cells show <50%. Also, the rows of cells are not next to each other,
they are D2736 and F27:F36. And sometimes F27:F36 will not have any results
in it (it will be "N/A"). Am I trying to do too much? Thanks!!!!

 

[Bob Phillips]

=SUM(COUNTIF(INDIRECT({"d2736","F27:F36"}),">0.5"))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob, but then how do I get it to say "yes" if the correct percentage
(90% of the entries) is >=0.5? Also, it won't always be a minimum of 18, if
only one row is filled out, then to pass, it would just need 8.

 

[john.hoft-march]

Thanks Bob, but then how do I get it to say "yes" if the correct percentage
(90% of the entries) is >=0.5? Also, it won't always be a minimum of 18, if
only one row is filled out, then to pass, it would just need 8.





- Show quoted text -

I'd say you need to insert an interim results column, something like
=IF(d27>=50%,if(f27>=50%,1,0,0), or however you want to test the data
for the two columns. The nested IF statements would bring back a
value of 1 if both cells had values greater than 50%, otherwise 0.
Then your results formula refers to the interim results column and is
something like =IF(sum(interim column)>=.
9*(count(d27:d36)+count(f27:f36)),"yes","no").

Good luck.
John

 

[Guest]

Awesome, thanks!

I'd say you need to insert an interim results column, something like
=IF(d27>=50%,if(f27>=50%,1,0,0), or however you want to test the data
for the two columns. The nested IF statements would bring back a
value of 1 if both cells had values greater than 50%, otherwise 0.
Then your results formula refers to the interim results column and is
something like =IF(sum(interim column)>=.
9*(count(d27:d36)+count(f27:f36)),"yes","no").

Good luck.
John