Feb 02 2022 12:01 PM
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
Feb 02 2022 12:03 PM
Feb 02 2022 10:01 PM
@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 nonmedical 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 straightaway.
For the overall "score", you could use something like:
=SUM(C2:C13)/COUNT(C2:C13)
and format the cell as a percentage.
Feb 03 2022 07:00 PM
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 1100%, 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
Feb 03 2022 09:43 PM  edited Feb 03 2022 09:44 PM
@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:
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.
Feb 04 2022 07:14 AM
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
Feb 04 2022 10:57 AM
Solution@SEAK1 See if the attached file has everything as it should be. If not, let me know.
Feb 04 2022 06:27 PM
Feb 09 2022 07:03 PM
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 followup 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
Feb 10 2022 04:55 AM
Thank you so much Riny! Your formulas worked terrifically! Have a wonderful week and weekend! Take care.
Kind Regards,
Steve