Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have 2 cells containing a % value. The value for both is 9%. I want to conditionally format these cells as duplicate values but excel only highlights them as duplicate values if the decimal point is exactly the same. How can i correct this?
Solved by S. C. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 24/05/2018 - 11:54
User 24/05/2018 - 11:54
Excelchat Expert 24/05/2018 - 11:54
Welcome to excelchat.
Excelchat Expert 24/05/2018 - 11:55
I want to understand the problem clearly.
User 24/05/2018 - 11:55
Excelchat Expert 24/05/2018 - 11:55
Can you please show me the exact values in those cells?
User 24/05/2018 - 11:56
the values in the cells are 8.9% and 9.1%
Excelchat Expert 24/05/2018 - 11:57
And you want those to treat as duplicate even though there are not exactly the same?
User 24/05/2018 - 11:57
Excelchat Expert 24/05/2018 - 11:58
This seems a bit confusing, please tell me more about what you are trying to do and what you have.
User 24/05/2018 - 11:59
I have 2 columns of values in percentages
Excelchat Expert 24/05/2018 - 11:59
It would be better if you can show me the file too.
User 24/05/2018 - 11:59
now and again the same value appears in both columns - for example : 9%
User 24/05/2018 - 12:00
I want excel to highlight these values as duplicates but excel reads to the decimal place and won't highlight them as duplicates because the decimal point is different - eg: 8.9% and 9.1%
Excelchat Expert 24/05/2018 - 12:01
I understand.
User 24/05/2018 - 12:01
User 24/05/2018 - 12:01
Excelchat Expert 24/05/2018 - 12:01
And I need to see all values to suggest a solution.
Excelchat Expert 24/05/2018 - 12:02
Can you please send me the file?
User 24/05/2018 - 12:02
can I type out a typical example for you?
Excelchat Expert 24/05/2018 - 12:03
I would need more than a few examples.
User 24/05/2018 - 12:04
I thought there would be quite an easy solution such as telling excel to ignore the decimal points?
Excelchat Expert 24/05/2018 - 12:05
Okay, in the given example, you said the numbers are 8.9 and 9.1 So ignoring decimals would give you 8 and 9 respectively.
Excelchat Expert 24/05/2018 - 12:06
INT function tells excel to ignore decimals. But if we use that in this case, you will get 8 and 9 which are not the same.
User 24/05/2018 - 12:06
Dosen't excel round up to the nearest whole number 8.9 = 9 and 9.1 = 9
Excelchat Expert 24/05/2018 - 12:06
And therefore won't be identified as duplicates.
User 24/05/2018 - 12:06
User 24/05/2018 - 12:07
so is there no other way?
Excelchat Expert 24/05/2018 - 12:07
The automatic rounding in excel only shows you the rounded values but the exact values remain same.
Excelchat Expert 24/05/2018 - 12:07
Yeah there is, that is why I need to see some example.
Excelchat Expert 24/05/2018 - 12:08
I'm again saying it would be better if I can see the file itself.
Excelchat Expert 24/05/2018 - 12:08
making sense?
Excelchat Expert 24/05/2018 - 12:08
If you are sensitive about your data, I do understand that.
User 24/05/2018 - 12:09
yes but you could easily create my scenario without me sending you the file - its confidential
Excelchat Expert 24/05/2018 - 12:10
I understand your feeling and that you data is confidential. Although I can work without the original data, it would require more time and in some cases the solution wouldn't be ideal or efficient.
User 24/05/2018 - 12:12
Its probably more complicated than i thought it would be so I'm not sure it is worth finding a solution - I thought I may be missing something obvious
Excelchat Expert 24/05/2018 - 12:12
Please first try this formula in your cell
Excelchat Expert 24/05/2018 - 12:12
Excelchat Expert 24/05/2018 - 12:13
=ROUND(A1,0) this will round numbers to nearest whole number.
Excelchat Expert 24/05/2018 - 12:13
And eliminate decimals.
User 24/05/2018 - 12:13
that sounds about right
Excelchat Expert 24/05/2018 - 12:13
Of course you have to change the A1 in the formula accordingly your file.
Excelchat Expert 24/05/2018 - 12:14
And after that you can try the conditional formatting.
Excelchat Expert 24/05/2018 - 12:14
Let me know whether that works or not.
User 24/05/2018 - 12:14
what do I put in, instead of A1?
Excelchat Expert 24/05/2018 - 12:15
The cell reference of the value you want to round to nearest whole number.
User 24/05/2018 - 12:15
I see
Excelchat Expert 24/05/2018 - 12:15
Okay. Let me know if you have anymore questions.
User 24/05/2018 - 12:16
that formula you have quoted is similar to other suggestions I have seen when trying to find a solution to this
User 24/05/2018 - 12:16
Thanks for your help
Excelchat Expert 24/05/2018 - 12:16
This resolves your issue?
User 24/05/2018 - 12:17
Its the best one I've come across - will try it later
Excelchat Expert 24/05/2018 - 12:17
Sounds good.
User 24/05/2018 - 12:17
does this service cost anything?
Excelchat Expert 24/05/2018 - 12:17
if you have any confusion, you can ask me.
Excelchat Expert 24/05/2018 - 12:18
Yeah, we do charge by session. The updated pricing is listed on the website.
User 24/05/2018 - 12:18
my first time here - is this one free?
Excelchat Expert 24/05/2018 - 12:19
I thing so, but if it were to cost you money then you already would have asked to enter your payment details.
User 24/05/2018 - 12:20
well thanks again
Excelchat Expert 24/05/2018 - 12:20
As an expert, my task is to solve user problem. We experts don't always have the latest pricing and others things.
Excelchat Expert 24/05/2018 - 12:21
Thank you for trying out got it pro excel. Have a great day ahead!
User 24/05/2018 - 12:21
you yoo.
User 24/05/2018 - 12:21
you too
Excelchat Expert 24/05/2018 - 12:24
The session will end in 10 minutes unless you opt to end it before. Thanks again for visiting us today.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc