0

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.

tourist
  • 4,165
  • 6
  • 25
  • 47
Piper Ramirez
  • 373
  • 1
  • 3
  • 11
  • Do you want to change the values or the displayed number format? –  Jun 27 '18 at 14:23
  • @PiperRamirez I'm a bit confused at what you're trying to achieve, but from the first glance it looks like you're attempting to use the [**`ROUNDDOWN(number, digits)`**](https://www.techonthenet.com/excel/formulas/rounddown.php) function – Samuel Hulla Jun 27 '18 at 15:55

2 Answers2

0

If you just want to change the displayed number format then this should do.

Sub cfrZeroes()
    With Worksheets("sheet2").UsedRange.Cells
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
          Formula1:="=ROUND(ABS(" & .Cells(1).Address(0, 0) & "),2)<=0.01"
        .FormatConditions(.FormatConditions.Count).NumberFormat = "\0"
    End With
End Sub

That will change -0.015 to 0.015 to look like 0 but it won't change the underlying raw value.

  • Why resort to VBA if you can just use standard conditional formatting? – Zack Jun 27 '18 at 14:58
  • @Zack - because, quite frankly, it is easier to demonstrate in vba and let the user use cf, manage rules to see what was accomplished. –  Jun 27 '18 at 15:00
  • Still confused as how I can use conditional formatting, but if I were to use the VBA code could I simply copy and paste it and it would work ok? – Piper Ramirez Jun 27 '18 at 15:09
0

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
Zack
  • 2,220
  • 1
  • 8
  • 12
  • Thank you! I think this is exactly what I needed! – Piper Ramirez Jun 27 '18 at 15:56
  • would the conditional formatting still apply if I were to refresh the data in hyperion essbase? – Piper Ramirez Jun 27 '18 at 17:14
  • I'm not familiar with Hyperiod Essbase, so couldn't really say. As long as the data is going into the same spreadsheet (where the conditional formatting is applied), you should be good. – Zack Jun 27 '18 at 17:17