Formula for using integral values in a chart, blank spaces, and looking for overall concordance.

[SEAK1]

Hi,

I have been working on this for days, and still have not been able to figure this out.  I am definitely not an Excel expert.

I have difficulty with coming up with the Concordance values. 

There are blank spaces in either the ER or PR columns which I need to ignore in the calculation of the concordance.  (Usually, I end up having too many zeros in the concordance column.)

Also, when the ER or PR values are <1 , it is hard for me to figure out how to handle these numbers and still end up with the appropriate value in the Concordance column. 

ER(%)	PR(%)	ER/PR Concordance 1=Yes 0=No
99	82	1
96	86	1
95	9	1
<1	4	0
0	0	1
95	0	0
0	0	1
100	<1	0

And then, finally, i need to calculate the overall percentage concordance…..       6/8 x 100 = 75%

Thank you so much.  I really appreciate it.

Sincerely yours, 

Steve

[#2]

Oops....the overall concordance above should be 5/8 x 100 = 62.5%

[#3]

@SEAK1 I had to Google for ER% and PR% in combination with concordance and understand that you are dealing with comparing two hormone levels relating to breast cancer research and that you need to determine a degree of correlation between the two. Forgive me for my non-medical interpretation of this Excel problem.

The blanks can be ignored by using, for instance:

=IF(A2="","",<calculation>), where the <calculation> bit would contain the formula for concordance in cases where A1 in not empty. Or perhaps you should just not have empty rows. All that is left is for you to explain the end result should be "Yes" and when "No".

With regard to values less than 1, i.e. "<1", how do these relate to zero? Since zero is also less than 1. Or does it mean that zero means "nothing at all" and "<1" means "something but just very little". If so, then get rid of the <-sign and use e.g. 0.5 which is a number half way between 0 and 1. That will at least enable you to use it in a calculation straight-away.

For the overall "score", you could use something like:

=SUM(C2:C13)/COUNT(C2:C13)

and format the cell as a percentage.

[#4]

@Riny_van_Eekelen 

Hi Riny,

Thank you so much for your very prompt and kind reply!   The formula you suggested to calculate the "concordance score" is very helpful. 

With regards to your question, we are looking at breast tumor percentage nuclear ER and PR reactivity in tissue biopsies.  The cutoff of 1% is positive.  When a patient has <1% expression, we are saying that only rare tumor cells are positive, but since the value is less than 1% of the area examined, it is reported out as being Negative.    In contrast, when tumors are 1-100%, they are considered positive.  I was wondering if there was a way to write a formula so the concordance can be determined by Excel.  Something like....

A1 is the ER column, and A2 is the PR column.   

=IF(A1>=1 and A2>=1, IF(A1=0 and A2 = 0, IF(A2<1 and A2<1))), "Yes", "No"  

but that did not work.  Any suggestions would be really appreciated.  Thank you Riny!

Sincerely yours,

Steve

[#5]

@SEAK1 You have me confused now, but that may just be Excel terminology. A1 is the cell in the first column (A) on the first row (1). Thus A2 is the cell directly below it. I assume that the pairs you want to examine are on the same row. ER% from A1 and EP% from B1. Correct?

With regard to the concordance formula I'm still not sure about the logic. Let's not talk about cell references, but say it in words. This is what I think your example is saying:

• If both ER and EP are greater than 1, then Yes.
• If either one of ER or EP are less than 1 (and that includes zero), then No.

but if both ER and EP are zero your schedules suggests a Yes. That I don't get. Perhaps I'm just slow. Sorry. Perhaps you can clarify that bit and correct me where I go wrong.

[#6]

Hi Riny, 

Sorry about that.  You are absolutely correct, it is much better for me to say it in text. 

-- If both ER and PR are greater than or equal to 1, then “Yes” Concordance.

-- If both ER and PR are less than 1 (including zero), then “Yes” Concordance.

-- If ER greater or equal to 1, and PR < 1 (including zero), then “No” Concordance.   

-- If ER < 1 (including zero), and PR greater or equal to 1, then “No” Concordance.   

-- If “Blank” space, then leave “blank” space in the Concordance column. 

--Finally, calculate the overall Concordance at the bottom of the third column…..

Number of “Yes” divided by total number of “Yes” and No”, and express as a “percentage Overall Concordance”. 

I hope this helps.  Again, Riny, I really appreciate your kindness in helping me with this.

Best Regards,

Steve

[#7 · Accepted solution]

@SEAK1 See if the attached file has everything as it should be. If not, let me know.

[#8]

Hi Riny,
Your formula worked perfectly and gave me the results I needed! There is no way I would have been able to figure it out with your help! I really appreciate it! If I can help you in any way, please feel free to contact me. Thank you again!
Best Regards,
Steve

[#9]

@SEAK1 Glad I could help!

[#10]

@Riny_van_Eekelen 

Age	ER  (%)	PR  (%)	ER/PR Concordance
81	99	82	Yes
78	96	86	Yes
73
68
64	95	9	Yes
73	97	44	Yes
71
59	0	0	Yes
46	0	79	Yes
75	99	99	Yes
52	99	0	No

 Hi Riny,

I hope all is well.  I am so sorry to bother you, but may I please ask you follow-up questions in order to obtain some other data that we need.

--Assuming my chart goes from line 4 to line 10,000, how can I calculate the percentage of “Yes” in the ER/PR concordance for all these 10,000 lines? 

Only the Yes’s and No’s should be in the calculation, and skipping the blank spaces (where no ER/PR testing was performed.)

Most will certainly be “blank or empty” spaces but I wanted to have the flexibility for future testing.

 In the example above, it should be 87.5%.  (7 Yes / 8 Yes + No)

--Finally, if a woman is < 65 years old, how can we calculate the percentage of “ER Negative cases” (where ER % is <1 by definition) from line 4 up to line 10,000 ?   

(ER Positive cases would be when ER > 1 ).  In the example above, it should be  25%  (  2 / 8  ). 

I am having data from line 4 up to line 10,000 to accommodate future testing.  

Thank you kindly Riny.  I really appreciate it.

Sincerely yours, 

Steve

[#11]

@SEAK1 No problem. See attached.

[#12]

@Riny_van_Eekelen 

Thank you so much Riny!  Your formulas worked terrifically!  Have a wonderful week and weekend!  Take care.  

Kind Regards,

Steve