Forum Discussion
Formula for using integral values in a chart, blank spaces, and looking for overall concordance.
SEAK1 See if the attached file has everything as it should be. If not, let me know.
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.
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
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.
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
SEAK1 See if the attached file has everything as it should be. If not, let me know.