LinkBack URL
About LinkBacks
Can someone please help me in creating a formula for cell A3 that does the following:
IF A1-A2 is less than 0, return 0 OR If A1-A2 is more than 0, round up to the closest whole number and divide by 10.
Thanks!
Can someone please help me in creating a formula for cell A3 that does the following:
IF A1-A2 is less than 0, return 0 OR If A1-A2 is more than 0, round up to the closest whole number and divide by 10.
Thanks!
Hello Scott,
Does this give you the correct results?
=IF(A1-A2<0,0,ROUND(A1-A2,-1)/10)
Diana
Let me give it a try now and let you know. Thanks for such a quick response, Diana.
Please Login or Register to view this content.
It worked when the number came out less than 0 but in instances where the result is more than 0, the cell still shows 0.
Test 1: A1=14 A2=30
Test 2: A1=30.6 A2=30
My last reply was to Diana. Let me give your formula a try now bentlybob.
Bob, test 1 worked fine. Test 2 returned .1 How would I make the formula return only whole numbers? In this case it should be 1.
try
=ROUND(MAX(0,A1-A2),0)/10
edit: the answer is not 1 as stated above it should be .1 because you have to divide 1 by 10 as per your request to divide by ten , isn't?
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hi Vlady. Thanks for the reply. I'm getting the same results as I did with bentlybob's formula. If A1=30.6 and A2=30, I get .1 It should be 1.
Try =IF(A1>A2,ROUND(A1-A2,-1)/10,0)
Diana
if you expect the result's as whole number in this case you should delete the /10 in their/my formula
in your first post.
if A1-A2 is greater than or equal 10 then your get whole number if you divide by ten, if lower than 10 then the answers will all be decimals., round up to the closest whole number and divide by 10.
For clarification, if A1-A2= a figure between 30.1 and 40, the answer should be 1. If the figure is between 40.1 and 50, the answer should be 2, and so on.
Thanks.
Yes, even if you use ROUNDUP, it rounds to 1 and then, when you divide by 10, you get .1. Perhaps you want the rounding up done AFTER you divide by 10?
This appears to satisfy your latest clarification.Please Login or Register to view this content.
Last edited by bentleybob; 06-21-2012 at 11:23 PM.
bentlybob your formula is working when A1=30.6 and A2=30 but when A1=14 and A2=30 I'm getting a "FALSE"
Oops, sorry. Forgot the last piece.
Please Login or Register to view this content.
We have a winner! Thanks to all for your help.
@ scott
so divide it first before rounding.. not round then divide as you stated in the first post...you can do it also to the other formulas given above.
thanks for the rep also.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks