# Using Negative Percentages in formulas

#### Kmbalderamos

##### New Member
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

##### Well-known Member
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.

Last edited:

#### joeu2004

##### Well-known Member
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.

Last edited:

#### Kmbalderamos

##### New Member
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.

#### Kmbalderamos

##### New Member
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

##### Well-known Member
-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

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

1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...