Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indexing/Matching True/False results
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 -- drvortex ------------------------------------------------------------------------ drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896 View this thread: http://www.excelforum.com/showthread...hreadid=487679 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indexing/Matching True/False results
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 wrote: 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 -- drvortex ------------------------------------------------------------------------ drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896 View this thread: http://www.excelforum.com/showthread...hreadid=487679 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indexing/Matching True/False results
I tried to do that function and came up with #VALUE! error. Could you send me your email. Unable to attach file. Thanks for your help. I'm trying to understand the logic. Jason -- drvortex ------------------------------------------------------------------------ drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896 View this thread: http://www.excelforum.com/showthread...hreadid=487679 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indexing/Matching True/False results
My test spreadsheet is attached. Let me know if it solves your problem. Declan O'R +-------------------------------------------------------------------+ |Filename: True_False.zip | |Download: http://www.excelforum.com/attachment.php?postid=4070 | +-------------------------------------------------------------------+ -- DOR ------------------------------------------------------------------------ DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088 View this thread: http://www.excelforum.com/showthread...hreadid=487679 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indexing/Matching True/False results
Thanks man. I got it to work. Its awesome, saves a ton of time and makes our metrics much more detailed. Thanks again. -- drvortex ------------------------------------------------------------------------ drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896 View this thread: http://www.excelforum.com/showthread...hreadid=487679 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indexing/Matching True/False results
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 -- DOR ------------------------------------------------------------------------ DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088 View this thread: http://www.excelforum.com/showthread...hreadid=487679 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
abdualmohsn | Excel Discussion (Misc queries) | |||
Date stamp formula results? Tricky problem? | Excel Discussion (Misc queries) | |||
calculating results in formulas | Excel Discussion (Misc queries) | |||
Convert TRUE/FALSE results to a number | Excel Worksheet Functions | |||
?odd results for =left(F#,2) | Excel Worksheet Functions |