Using Negative Percentages in formulas

Kmbalderamos

New Member
Joined
Oct 24, 2019
Messages
4
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
Joined
Mar 31, 2015
Messages
2,048
Office Version
365
Platform
Windows
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]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">-45%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFF2CC;;">-100%</td><td style="background-color: #FFF2CC;;">stable</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">table</td><td style="background-color: #E2EFDA;;">stable</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFF2CC;;">-30%</td><td style="background-color: #FFF2CC;;">partial</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">hard-coded</td><td style="background-color: #C6E0B4;;">stable</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFF2CC;;">20.0000001%</td><td style="background-color: #FFF2CC;;">progressive</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet45</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G12</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G10,I11:J13,2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G13</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G10,{-1,"stable";-0.3,"partial";0.200000001,"progressive"},2</font>)</td></tr></tbody></table></td></tr></table><br />[/FONT]
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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
Joined
Oct 24, 2019
Messages
4
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]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">-45%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFF2CC;;">-100%</td><td style="background-color: #FFF2CC;;">stable</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">table</td><td style="background-color: #E2EFDA;;">stable</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFF2CC;;">-30%</td><td style="background-color: #FFF2CC;;">partial</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">hard-coded</td><td style="background-color: #C6E0B4;;">stable</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFF2CC;;">20.0000001%</td><td style="background-color: #FFF2CC;;">progressive</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet45</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G12</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G10,I11:J13,2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G13</th><td style="text-align:left">=VLOOKUP(<font color="Blue">G10,{-1,"stable";-0.3,"partial";0.200000001,"progressive"},2</font>)</td></tr></tbody></table></td></tr></table><br />[/FONT]
 

Kmbalderamos

New Member
Joined
Oct 24, 2019
Messages
4
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
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
-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"))
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top