Indexing/Matching True/False results

[drvortex]

Hello all,

I'm having a problem figuring out how to combine TWO functions to work
properly. What I'm trying to do is the following:

My chart looks like the following

B C D F G H J K
L

Date Name Call Sign Fcst Obs Correct? Fcst Obs Correct?

2 JN Tuff n i FALSE n n
TRUE
4 JN Sku i i TRUE i n
FALSE

Ok...the letters are column that the data is located in. The current
function I'm using is:

=IF(ISERROR((COUNTIF(H9:H33,"TRUE"))/((COUNTIF(H9:H33,"TRUE")+(COUNTIF(H9:H33,"FALSE"))))),"",(COUNTIF(H9:H33,"TRUE"))/((COUNTIF(H9:H33,"TRUE")+(COUNTIF(H9:H33,"FALSE")))))

What this does is takes all the FALSEs and TRUEs and counts them all up
and then divides them to give a decimal value at the bottom where this
function is located. What I need help on is adding another function to
this to count ONLY the values (FALSE and TRUE) that is specified by the
NAME.

So, what I'm asking is to have all JNs answers in the Correct? line
added up not all of the results from the top to the bottom. I need to
system to only count up the results of the specified name.

Can anyone help me out on this??? If you need more info, please let me
know. I can email you the file so you can take a look at it. This is
important! Thanks in advance.

Jason

 

[DOR]

Assuming that all of a person's answers are either TRUE or FALSE, put
the name in K1 and try this formula:

=IF(COUNTIF(C9:C33,K1)>0,SUMPRODUCT(--(C9:C33=K1),--(H9:H33))/COUNTIF(C9:C33,K1),"")

If you change the TRUE/FALSE indication to anything else, such as T or
F, you will need to change the formula to:

=IF(COUNTIF(C9:C33,K1)>0,SUMPRODUCT(--(C9:C33=K1),--(H9:H33="T"))/COUNTIF(C9:C33,K1),"")

because Excel treats the words TRUE and FALSE differently from any
other words, and it is not necessary to have an equality test to
determine if a cell has a TRUE value; it is de facto TRUE.

If it doesn't work, send me your spreadsheet.

HTH

Declan O'R

 

[drvortex]

I tried to do that function and came up with #VALUE! error. Could yo
send me your email. Unable to attach file. Thanks for your help. I'
trying to understand the logic.

Jaso

 

[DOR]

My test spreadsheet is attached. Let me know if it solves you
problem.

Declan O'

+-------------------------------------------------------------------
|Filename: True_False.zip
|Download: http://www.excelforum.com/attachment.php?postid=4070
+-------------------------------------------------------------------

 

[drvortex]

Thanks man. I got it to work. Its awesome, saves a ton of time an
makes our metrics much more detailed. Thanks again

 

[DOR]

Hi,

You asked another question regarding how to count all the TRUEs plus
all the FALSEs in a private message - on case you don't go there rto
get my response, here it is ...

I may be wrong in this, but I was assuming that every instance of a
particular Name would have either a TRUE or a FALSE answer to each
question, so my formula for the percentage just counts the TRUEs and
divides by the number of occurrences of the Name, which in my formula
was

=COUNTIF(C9:C33,K1)

which simpy counts the occurrrenses of name. This should give you the
total number of TRUEs and FALSEs, but, if it is possible that a name
would have a blank in the TRUE/FALSE column, we have a different
situation that I would need to think about.

Let me know if there is such a third possibility.

Regards

Declan O'R