Search code examples
excelfunctionformulahyperionessbase

How do you add a rule to cells in excel?

I have a set of data extracted from Hyperion Essbase in which I would like to add a rule over those cells. I've tried conditional formatting, but I'm not getting what I want.

For example some cells contain 0.000 or 13.5232 and the rule I want over them is IF(ROUND(ABS(Cell Value),2)<=0.01,0,Cell Value)

I'm not sure if my formula is the one that doesn't make sense or I'm not using conditional formatting correctly. I don't need any cells highlighted, I would just like the data in the cells rounded if they fit the criteria in my formula.

Solution

  • From your question, it seems like you want to use conditional formatting to change how the numbers are formatted (for numbers whose absolute value, rounded to 2 decimal places, is <= 0.01, they should be formatted with 0 decimal places, i.e., show as 0).

    This can absolutely be achieved with conditional formatting (no VBA required).

    1. Select the cells you wish to apply the conditional formatting to.
    2. Open the Conditional Formatting Rules Manager, and create a new rule.
    3. Select Use a formula to determine which cells to format, enter the formula that identifies the cells you wish to format (make sure you eschew absolute references). add formatting rule
    4. Click the Format button and select Number > 0 Decimal places (here)
    5. Click OK 3 times to return back to the spreadsheet, and you should see the values formatted as desired: final data