Display Plus Sign For Positive Numbers

[Andie]

If you have a formula lets say (sum A1-A2) and the total is negative i.e. A1 is 100, A2 is 50 would return -50, how do you get the value to show a plus sign if the value is positive? i.e. if A2 is 100 and A1 is 50, excel would simply show 50, but I'd like it to show +50.

Also, if the result is 0, so both A1 and A2 are 50, how do I get Excel to display the words On Forecast in a cell?

Thanks in advance

Andie

[carlmack]

One possibility would be to use the custom number format. Right Click > Format Cells > Number Tab. By selecting the Custom category you can enter a customised format string in the Type box.

You would need something like +#;-#;"On Forecast" or if you wanted to show one decimal +#.0;-#.0;"On Forecast".

HTH
Carl

[Andie]

Brilliant, thats worked Carl, however, before I applied it the number if greater than a thousand used to display with a comma as a separator and now it doesn't, i.e. it was 1,000, now it's 1000, do you know how I add the , back in?

[ByTheCringe2]

Use

+#,###;-#,###;"On Forecast"

[Andie]

Bingo

Thanks ByTheCringe2!

[carlmack]

ByTheCringe2,
Thankyou for stepping in,

Regards
Carl

[ByTheCringe2]

Cheers, each.

Carl, I learnt a bit doing that!