Home

Conditional Formatting a specific price range.

%3CLINGO-SUB%20id%3D%22lingo-sub-811628%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20a%20specific%20price%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811628%22%20slang%3D%22en-US%22%3EI%20have%20a%20data%20table%20(location%20across%20the%20top%2C%20product%20down%20the%20side)%20with%20different%20price%20showing%20for%20each%20combination.%20I'm%20trying%20to%20figure%20out%20a%20way%20to%20highlight%20cells%20that%20the%20price%20ends%20in%20%24x.x4.%20Can't%20figure%20out%20the%20conditional%20formatting%20formula%20for%20it.%20Thanks.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-811628%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811800%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20a%20specific%20price%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811800%22%20slang%3D%22en-US%22%3EMaybe%20I%20didn't%20make%20myself%20clear.%20I'm%20not%20trying%20to%20round%20out%20to%20%224%22.%20I'm%20trying%20to%20highlight%20a%20cell%20if%20the%20price%20ends%20in%20%224%22.%20%241.99%20would%20not%20be%20highlighted%2C%20but%20%241.94%20would%20be.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811878%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20a%20specific%20price%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394742%22%20target%3D%22_blank%22%3E%40AldoV34%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20what%20it%20do%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20468px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127556i0A8A13ADF19C0B9F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20%2B0%20correction%20to%20convert%20text%20to%20number%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811735%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20a%20specific%20price%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394742%22%20target%3D%22_blank%22%3E%40AldoV34%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20your%20prices%20are%20numbers%2C%20not%20texts.%20The%20formula%20could%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DRIGHT(ROUND(B2%2C2)*100)%2B0%3D4%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20B2%20is%20top%20left%20cell%20of%20the%20prices%20range%20(without%20headers)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811887%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20a%20specific%20price%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811887%22%20slang%3D%22en-US%22%3EGot%20it!%20Thanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811896%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20a%20specific%20price%20range.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811896%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394742%22%20target%3D%22_blank%22%3E%40AldoV34%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
AldoV34
New Contributor
I have a data table (location across the top, product down the side) with different price showing for each combination. I'm trying to figure out a way to highlight cells that the price ends in $x.x4. Can't figure out the conditional formatting formula for it. Thanks.
5 Replies

@AldoV34 

I hope your prices are numbers, not texts. The formula could be

 

=RIGHT(ROUND(B2,2)*100)+0=4

 

if B2 is top left cell of the prices range (without headers)

Maybe I didn't make myself clear. I'm not trying to round out to "4". I'm trying to highlight a cell if the price ends in "4". $1.99 would not be highlighted, but $1.94 would be.

@AldoV34 

That's what it do

image.png

with +0 correction to convert text to number

Got it! Thanks.

@AldoV34 , you are welcome

Related Conversations