Hello all!



Thank you for stopping by. I have a formula question:

Let's say I have two numbers: one number is in Column A and the other in Column B. The number in Column A is a whole number (ex., 5) and the number in Column B is a decimal (ex., 10.4). What formula could I use to compare Column A to Column B and display the whole number in Column C?

I really appreciate your assistance.

God Bless!

Rey

Hi Rey,

Can you explain that a bit more... what does "compare" the numbers mean? If you only ever want to display the whole number in column C, then wouldn't it always be the number from column A?

Perhaps you could give two or three examples and also tell us the result you want for each case?

Thanks

Thanks Colin for your time.

Well, lets say you have the following:

Column A | Column B | Column C
1.25 5 5
1.5 6 6
1.75 7 7
2 8 2
2.25 9 9


I would like Excel to compare the number in Column A with the number in Column B and decide which number does NOT have a decimal and put that result in Column C. If both numbers are whole numbers, then put the smaller number in Column C.

For example, in row 1, Column C should display 5 because it is not a decimal. For row 2, Column C should display a 6 because it is not a decimal. In row 4, Column C should display a 2. Since both numbers are whole numbers, the smaller number would be the correct number.

HTH's.

Rey

Hi Rey,

Thanks, that explains it well. You don't say what should be returned if neither number is a whole number, so I will assume that at least one of them always will be.

Thanks Colin!

I will give this a try and will let you know what happens.

In my situation, there will always be a whole number.

Thanks again!

Rey

Hey Colin,

That's awesome! This worked great! Thank you sooooooooooo much.

Can you explain to me what each section of the formula means?

Rey

Hi,

Sure.

First of all, we need a way to check if the number is a whole number. Excel doesn't have a built-in which will do that. However, it does have a few functions which can be used to remove the decimal portion of a number: the most common would be INT() and TRUNC(), but there are also various rounding functions too (such as ROUND(), ROUNDDOWN(), etc....). The logic I used was to take the number from each cell, remove the decimal portion of the number and then check to see if that is equal to the original number: if it is then the number must be a whole number:
I used TRUNC() rather than INT() so that this test would also work on negative numbers.


From there, it's just a case of applying the correct logic for the possible outcomes:

• Assume that at least one number must be whole
• Assume that we don't have to worry about empty cells

• If A1 and B1 are whole numbers, take the minimum value.
• Else, If A1 is whole and B1 is not whole then return A1.
• Else, A1 is not whole so B1 must be whole, so return B1.

Thanks Colin for your help. This formula is helping me a lot. I really appreciate your knowledge.

Rey Krauss

Hi Rey,

Comparing names that are alike, requires 'fuzzy matching', which is a task for vba.

You might care to look here for an idea of what's involved: http://www.mrexcel.com/forum/showthread.php?p=331454

Sometimes its easier to simply highlight the entries that aren't an exact match and eyeball them.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]