Forum Discussion
Formula for using integral values in a chart, blank spaces, and looking for overall concordance.
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
SEAK1 See if the attached file has everything as it should be. If not, let me know.
11 Replies
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.
- Oops....the overall concordance above should be 5/8 x 100 = 62.5%
