ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Add "+" plus symbol to positive percentage (https://www.excelbanter.com/excel-discussion-misc-queries/199687-add-plus-symbol-positive-percentage.html)

Hendrik.Kleine

Add "+" plus symbol to positive percentage
 
Hi, Im calculating a precentage of two figures:
A1= 100 A2=200

=(A1/A2)-1

This gives me a negative result "-50%"

When A1 is greater than A2, resulting in a positve result, I'd like it to
display "+50%". Right now the plus symbol is not included.

Tried to concatenate but can't figure it out.

Many thanks for all suggestions.
Hendrik


ExcelBanter AI

Answer: Add "+" plus symbol to positive percentage
 
Hi Hendrik,

To add the plus symbol to positive percentage, you can use the IF function in Excel. Here's how:
  1. Start by typing your formula in a cell:
    Code:

    =(A1/A2)-1
  2. Click on the cell where you want to display the result.
  3. Type the following formula:
    Code:

    =IF((A1/A2)-1=0,"+"&TEXT((A1/A2)-1,"0%"),TEXT((A1/A2)-1,"0%"))
  4. Press Enter.

What this formula does is check if the result of your original formula is greater than or equal to zero. If it is, it adds a plus sign to the beginning of the percentage using the "&" symbol to concatenate the plus sign with the percentage. If the result is negative, it simply displays the percentage with no plus sign.

Let me know if you have any further questions.

Duke Carey

Add "+" plus symbol to positive percentage
 
Well - if you WANT a positive result
you can eliminate the "-1" from your formula, or
you can use an IF() =A1/A2-IF(A2A1,1,0), or
you can use ABS(A1/A2-1)

Or, you can use a custom format

0.00%;"+"0.00%

that will put a plus sign in front of negative percentages. If you want it
in front of both...

"+"0.00%;"+"0.00%




"Hendrik.Kleine" wrote:

Hi, Im calculating a precentage of two figures:
A1= 100 A2=200

=(A1/A2)-1

This gives me a negative result "-50%"

When A1 is greater than A2, resulting in a positve result, I'd like it to
display "+50%". Right now the plus symbol is not included.

Tried to concatenate but can't figure it out.

Many thanks for all suggestions.
Hendrik


John C[_2_]

Add "+" plus symbol to positive percentage
 
Format|Cells|Custom
Custom number format:
+ #0.0# %;- #0.0# %

Hope this helps.
--
John C


"Hendrik.Kleine" wrote:

Hi, Im calculating a precentage of two figures:
A1= 100 A2=200

=(A1/A2)-1

This gives me a negative result "-50%"

When A1 is greater than A2, resulting in a positve result, I'd like it to
display "+50%". Right now the plus symbol is not included.

Tried to concatenate but can't figure it out.

Many thanks for all suggestions.
Hendrik


Hendrik.Kleine

Add "+" plus symbol to positive percentage
 
Thank you very much both, worked like a charm :)

"John C" wrote:

Format|Cells|Custom
Custom number format:
+ #0.0# %;- #0.0# %

Hope this helps.
--
John C


"Hendrik.Kleine" wrote:

Hi, Im calculating a precentage of two figures:
A1= 100 A2=200

=(A1/A2)-1

This gives me a negative result "-50%"

When A1 is greater than A2, resulting in a positve result, I'd like it to
display "+50%". Right now the plus symbol is not included.

Tried to concatenate but can't figure it out.

Many thanks for all suggestions.
Hendrik


John C[_2_]

Add "+" plus symbol to positive percentage
 
Thanks for the feedback.
--
John C


"Hendrik.Kleine" wrote:

Thank you very much both, worked like a charm :)

"John C" wrote:

Format|Cells|Custom
Custom number format:
+ #0.0# %;- #0.0# %

Hope this helps.
--
John C


"Hendrik.Kleine" wrote:

Hi, Im calculating a precentage of two figures:
A1= 100 A2=200

=(A1/A2)-1

This gives me a negative result "-50%"

When A1 is greater than A2, resulting in a positve result, I'd like it to
display "+50%". Right now the plus symbol is not included.

Tried to concatenate but can't figure it out.

Many thanks for all suggestions.
Hendrik



All times are GMT +1. The time now is 06:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com