I have this spreadsheet that I have been working on for a while now. In brief, there is a variable that is entered over time and based on this variable, I would like excel to calculate & define five different response categories : SD, MR, PR, MR, PD. I am expecting the variable to change over time, somewhat like a parabolic curve. It will likely first decrease and a few months increase.
During the initial reduction phase, I need to use a formula to determine these responses based on the % drop in the value from “the very first value” (cutoffs being 25%, 50%, 90% and 100%). For instance, if variable drops from 6.3 to 6.1, this will be a 3.2% drop and will qualify as SD. I used a formula in column E to do this (=(100-((B3*100)/$B$2)). The challenge is the following: Remember, I had mentioned that I am expecting the variable to hit bottom at some point and then trend up. When it hits the column value hits a lowest timepoint, I need the formula to change and instead calculate the % raise using this new lowest data value as a new reference point. But for all of these, I would like to have a single column (column H). For now, I have the following formula for H2:
=IF(AND(B3-$D$24>0.5,G3<-25), "PD", IF(G3<25,"SD",IF(G3<50,"MR",IF(G3<90,"PR",IF(G3<100,"VGPR",IF(G3=100,"CR"))))))
It would also be great if it could have a separate column which automatically saves the responses as “values” so that when additional variables are added over time, the responses from months / years ago will not be recalculated using the formula.
Here is a screenshot (apologize, my XL2bb is not working for some reason)
Thank you ahead of time
During the initial reduction phase, I need to use a formula to determine these responses based on the % drop in the value from “the very first value” (cutoffs being 25%, 50%, 90% and 100%). For instance, if variable drops from 6.3 to 6.1, this will be a 3.2% drop and will qualify as SD. I used a formula in column E to do this (=(100-((B3*100)/$B$2)). The challenge is the following: Remember, I had mentioned that I am expecting the variable to hit bottom at some point and then trend up. When it hits the column value hits a lowest timepoint, I need the formula to change and instead calculate the % raise using this new lowest data value as a new reference point. But for all of these, I would like to have a single column (column H). For now, I have the following formula for H2:
=IF(AND(B3-$D$24>0.5,G3<-25), "PD", IF(G3<25,"SD",IF(G3<50,"MR",IF(G3<90,"PR",IF(G3<100,"VGPR",IF(G3=100,"CR"))))))
It would also be great if it could have a separate column which automatically saves the responses as “values” so that when additional variables are added over time, the responses from months / years ago will not be recalculated using the formula.
Here is a screenshot (apologize, my XL2bb is not working for some reason)
Thank you ahead of time
