50790

How do you add a rule to cells in excel?

Question:

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.

Answer1:

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).

<ol><li>Select the cells you wish to apply the conditional formatting to.</li> <li>Open the <strong>Conditional Formatting Rules Manager</strong>, and create a <a href="https://i.stack.imgur.com/eviIl.png" rel="nofollow"><img alt="new rule" class="b-lazy" data-src="https://i.stack.imgur.com/eviIl.png" data-original="https://i.stack.imgur.com/eviIl.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>.</li> <li>Select <strong>Use a formula to determine which cells to format</strong>, enter the formula that identifies the cells you wish to format (make sure you eschew absolute references). <a href="https://i.stack.imgur.com/CBHDO.png" rel="nofollow"><img alt="add formatting rule" class="b-lazy" data-src="https://i.stack.imgur.com/CBHDO.png" data-original="https://i.stack.imgur.com/CBHDO.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a></li> <li>Click the <strong>Format</strong> button and select <strong>Number</strong> > <strong>0 Decimal places</strong> (<a href="https://i.stack.imgur.com/nKKQl.png" rel="nofollow"><img alt="here" class="b-lazy" data-src="https://i.stack.imgur.com/nKKQl.png" data-original="https://i.stack.imgur.com/nKKQl.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>)</li> <li>Click OK 3 times to return back to the spreadsheet, and you should see the values formatted as desired: <a href="https://i.stack.imgur.com/3xJy3.png" rel="nofollow"><img alt="final data" class="b-lazy" data-src="https://i.stack.imgur.com/3xJy3.png" data-original="https://i.stack.imgur.com/3xJy3.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a></li> </ol>

Answer2:

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.

Recommend

  • spark udf with data frame
  • How to parse given date string using moment.js?
  • Find text(A) and replace it with text(B) + HTML
  • how to sort the field in the mongo document which is inside array
  • Swift: Validate Username Input
  • Nhibernate QueryOver Orderby
  • Oracle: Using CTE with update clause
  • Simplifying the use of meshgrid in Matlab
  • How to resize image if the image is bigger than Textbox.But it not increase scale if smaller
  • jParallax trouble
  • Symfony2 redirect to https route fails (uses wrong port)
  • PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea
  • custom string delimiters stringtemplate-4
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • Making header act like google chrome mobile app address bar
  • How to merge keras sequential models with same input?
  • Prevent page break in text block with iText, XMLWorker
  • Can I read an iPhone beacon with Windows.Devices.Bluetooth.Advertisement.BluetoothLEManufacturerData
  • How to detect interior vertices in groups of 2d polygons? (E.g. ZIP Codes to determine a territory)
  • Convert Type Decimal to Hex (string) in .NET 3.5
  • Jackson Parser: ignore deserializing for type mismatch
  • Ajax Loaded meta Tags
  • Xamarin Forms - UWP Fonts
  • Splitting given String into two variables - php
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Sending data from AppleScript to FileMaker records
  • Deserializing XML into class C#
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • Build own AppleScript numerical error handling
  • Calling of Constructors in a Java
  • Arrays break string types in Julia
  • Traverse Array and Display in markup
  • How to format a variable of double type
  • Transpose CSV data with awk (pivot transformation)
  • Buffer size for converting unsigned long to string
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • Load html files in TinyMce
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?