Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-23-2011, 05:02 PM
djreyrey djreyrey is offline ** Excel Formula Windows 7 ** Excel Formula Office 2007
Novice
** Excel Formula
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default ** Excel Formula

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
Reply With Quote
  #2  
Old 03-23-2011, 05:23 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline ** Excel Formula Windows 7 32bit ** Excel Formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Reply With Quote
  #3  
Old 03-23-2011, 05:51 PM
djreyrey djreyrey is offline ** Excel Formula Windows 7 ** Excel Formula Office 2007
Novice
** Excel Formula
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 03-23-2011, 06:02 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline ** Excel Formula Windows 7 32bit ** Excel Formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Code:
=IF(TRUNC(A1)=A1,IF(TRUNC(B1)=B1,MIN(A1,B1),A1),B1)
Reply With Quote
  #5  
Old 03-23-2011, 06:42 PM
djreyrey djreyrey is offline ** Excel Formula Windows 7 ** Excel Formula Office 2007
Novice
** Excel Formula
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 03-23-2011, 09:06 PM
djreyrey djreyrey is offline ** Excel Formula Windows 7 ** Excel Formula Office 2007
Novice
** Excel Formula
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 03-24-2011, 02:30 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline ** Excel Formula Windows 7 32bit ** Excel Formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi,

Quote:
Can you explain to me what each section of the formula means?
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:
Code:
=IF(TRUNC(A1)=A1,"A1 is a whole number","A1 is not 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.
Code:
=IF(TRUNC(A1)=A1,IF(TRUNC(B1)=B1,MIN(A1,B1),A1),B1)
Reply With Quote
  #8  
Old 03-28-2011, 09:35 PM
djreyrey djreyrey is offline ** Excel Formula Windows 7 ** Excel Formula Office 2007
Novice
** Excel Formula
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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

Rey Krauss
Reply With Quote
  #9  
Old 04-15-2011, 03:55 PM
macropod's Avatar
macropod macropod is offline ** Excel Formula Windows 7 32bit ** Excel Formula Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to solve this formula in excel BrOkEn_iCe Excel 2 12-21-2010 05:45 AM
excel help Formula sellspeed Excel 15 03-10-2010 10:57 AM
Needs help to find excel formula- Please aamer_1983 Excel 2 07-13-2009 01:46 AM
Excel Formula Help masoom84 Excel 1 03-07-2009 09:41 AM
** Excel Formula Excel Formula Help Shahzad Excel 1 12-07-2008 04:13 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft