Using Negative Percentages in formulas

[Kmbalderamos]

Hi Everyone, I’m trying to create a formula that uses both negative and positive percentages and doesn’t have any false statements. I would like it to be if cell G10 is >= -30% then G12 should display “partial response”, if G10 is <-30% then G12 should display “stable disease” or if G10 is >+20% then G12 should display “progressive disease”. Please help!

 

[DRSteele]

Welcome to the forum.

Nested IF statements get ugly. What is superior is a lookup table.

I assume nothing can go below -100%. So then the bands are -100% to -30%, from or equal to -30% up to and including 20%, and then over 20% (so I added a tiny portion to it). You can either keep the lookup table in the Workbook somewhere, or just hard-code it into the function VLOOKUP.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	F	G	H	I	J
10		-45%
11				-100%	stable
12	table	stable		-30%	partial
13	hard-coded	stable		20.0000001%	progressive
Sheet45

Cell Formulas
Range	Formula
G12	=VLOOKUP(G10,I11:J13,2)
G13	=VLOOKUP(G10,{-1,"stable";-0.3,"partial";0.200000001,"progressive"},2)

[/FONT]

 

[joeu2004]

I would like it to be if cell G10 is >= -30% then G12 should display “partial response”, if G10 is <-30% then G12 should display “stable disease” or if G10 is >+20% then G12 should display “progressive disease”.

Your requirements are ambiguous: any value >20% is also >=-30%.

And just to be sure we're "speaking the same language" (of mathematics), do you agree that -40% is less than -30%?

Ostensibly, the following might work for you (in G12):

=IF(G10<-30%, "stable disease", IF(G10<=20%, "partial response", "progressive disease"))

I cannot image what is "ugly" about that. KISS!

One other comment.... If G10 is a calculated value, beware of surprises due to binary arithmetic anomalies and formatting.

 

[Kmbalderamos]

I will try this and report back! I tried to get a VLOOKUP to work but I couldn’t quite figure it out.

Welcome to the forum.

Nested IF statements get ugly. What is superior is a lookup table.

I assume nothing can go below -100%. So then the bands are -100% to -30%, from or equal to -30% up to and including 20%, and then over 20% (so I added a tiny portion to it). You can either keep the lookup table in the Workbook somewhere, or just hard-code it into the function VLOOKUP.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	F	G	H	I	J
10		-45%
11				-100%	stable
12	table	stable		-30%	partial
13	hard-coded	stable		20.0000001%	progressive
Sheet45

Cell Formulas
Range	Formula
G12	=VLOOKUP(G10,I11:J13,2)
G13	=VLOOKUP(G10,{-1,"stable";-0.3,"partial";0.200000001,"progressive"},2)

[/FONT]

 

[Kmbalderamos]

G10 is a calculated value, it’s the percent change from baseline (in mm) of the long axis of target lesions. These are tumor measurements based on either a CT scan or a MRI. -100% to - 30% are actually denoting a complete to partial tumor response. It’s measuring the percent change from a baseline measurement or brain and extracranial target lesions. -29.9999% to +19.99999% indicates stable disease and +20% or greater indicates progressive disease. It’s a bit confusing but it’s the way radiology reports are read. Any further help after this clarification is appreciated!

 

[joeu2004]

-100% to - 30% are actually denoting a complete to partial tumor response. [....] -29.9999% to +19.99999% indicates stable disease and +20% or greater indicates progressive disease.

So I wonder if you should explicitly round the calculation in G10 to 4 (or 5?) percentage decimal places, which is 6 (or 7?) decimal places.

If G10 is =expression, perhaps change it to =ROUND(expression,6), formatted as Percentage with 4 (or 5?) decimal places.

But only if "expression" returns only a numeric value. Otherwise, I suggest that you post the formula in G10, if you need help with rounding.

Note that merely formatting as Percentage with 4 (or 5?) decimal places usually affects only the appearance, not the actual value. (Unless you set "Precision as displayed", which I do not recommend.)

And it seems that your comparison operations are incorrect. -29.9999% to 19.99999% (5dp? typo?) is >-30% (not <-30%) and <20% (not <=20%).

So my formula should be:

=IF(G10<=-30%, "partial response", IF(G10<20%, "stable disease", "progressive disease"))

 

[Kmbalderamos]

I’ll try this tomorrow and report back, thanks!