Adding a character to a number cell based on a condition

  • Thread starter Thread starter Healingbear
  • Start date Start date
H

Healingbear

Simply, I'd like to add a "+" sign to a number in a cell depending on a
condtion.

More details:
I've written a formula to calculate the number of employees on any
given shift. It displays beautifully in the appropriate cell. Now I'd
like to add the plus sign to the number if a supervisor is on duty. I
can handle writing all the conditions, I just need to know how to
augment a cell value to include another character based on those
conditions.

(i.e. change 4 to 4+, 6.5 to 6.5+, etc...)

P.S. I'd really like the plus-sign, but would settle for anyway to
signify a supervisor presence in that number cell.

Thanks for any consideration and assistance.
 
Hi Healingbear!

Try something like:

=IF(A1="y",B1&"+",B1)

The trouble is that the amended cells will not be numbers now.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
What exactly in your spreadsheet denotes that a supervisor is on duty. We need
to know what to tie the evaluation criteria to.
 
If you need to be able to do math with the number in the cell AND have a
symbolic indicator in the same cell AND have the indicator
automatically set by a formula, I think the only way would be to
duplicate your numeric data in a hidden section of the spreadsheet
where you can do all the math and have the visible area with the
indicator be "for display purposed only" using Normans suggestion.

If you are willing to compromise:

Don't need to do math - just follow the suggestion posted by Norman

Don't need a symbol - use Conditional Formatting to shade the cell a
different color, change the typeface, outlining, etc.

Don't need the symbol in the cell - I do this all the time with up and
down arrows to indicate if the number has gone up/down compared to
previous months. I create a narrow column adjacent to the cell and use
my conditions to insert a text symbol (usually from one of the
Wingdings fonts).

Don't need to have it set automatically - Create a custom cell format
like this: 0.0"+" Then you can manually apply it to the approriate
cells. You will still be able to do math with the cell, but if the
supervisor condition changes, you have to manually change the cell
format.
 
Simply, I'd like to add a "+" sign to a number in a cell depending on a
condtion.

More details:
I've written a formula to calculate the number of employees on any
given shift. It displays beautifully in the appropriate cell. Now I'd
like to add the plus sign to the number if a supervisor is on duty. I
can handle writing all the conditions, I just need to know how to
augment a cell value to include another character based on those
conditions.

(i.e. change 4 to 4+, 6.5 to 6.5+, etc...)

P.S. I'd really like the plus-sign, but would settle for anyway to
signify a supervisor presence in that number cell.

Thanks for any consideration and assistance.


---
Click to expand...

If you add a character, then the value in the cell will no longer be a number
upon which you could perform math.

But you could use Conditional Formatting to color the cell; bold the entry; etc
and still be able to perform mathematical operations on the contents.


--ron
 
Back
Top