how to fix a formula

Jo H 20 Reputation points
2025-08-05T00:51:28.3333333+00:00

I have the below formula and I want to basically have:

less than 0 in red

more than 0 in green

equal to zero in orange

no decimal points

=IF(Table13[@February]=0, IF(Table13[@March]=0, 0, 1), TEXT((Table13[@March]-Table13[@February])/Table13[@February], "0.0%"))

Microsoft 365 and Office | Excel | For business | Windows
0 comments No comments

Answer accepted by question author
Darren-Ng 12,790 Reputation points Microsoft External Staff Moderator
2025-08-05T01:20:52.2666667+00:00

Hi @Jo H

Thank you for posting your question in the Microsoft Q&A forum.

To achieve your formatting and logic goals in Excel, you’ll need to adjust both the formula and apply conditional formatting. Here's how to do both:

This version removes decimals and simplifies the logic:

=IF(Table13[@February]=0, IF(Table13[@March]=0, 0, 1), ROUND((Table13[@March]-Table13[@February])/Table13[@February], 0))

This will return:

  • 0 if both February and March are zero
  • 1 if February is zero but March is not
  • Rounded percentage change (no decimals) otherwise

Besides that, Conditional Formatting Setup

  1. Select the column with the formula.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Format only cells that contain”.
  4. Set up three rules:

Red for less than 0

  • Rule: Cell Value < 0
  • Format: Font color red

Green for more than 0

  • Rule: Cell Value > 0
  • Format: Font color green

Orange for equal to 0

  • Rule: Cell Value = 0
  • Format: Font color orange

I hope it helps, if you have any other questions, please feel free to reach out.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.