Excel IF stement - rounding retail prices to "0.49"?
This is part of a larger IF statement to round my company’s product retails to the nearest 0.49 or 0.99 decimal (For example, if the item cost + gross margin was calculated as $2.51, we would sell the product to the customer for $2.49.)
I’m starting with the "0.49 part. If the "cents" value is between .25-.75, we would keep the "dollar" value the same and change the cents to 0.49 (as in the example above, a 2.51 price in cell A1 should result in 2.49 using my calculation). What am I doing wrong? Thanks for your help!
=IF(AND(Right(A1,2>25), Right(A1,2<75)), CONCATENATE(LEFT(A1,FIND(".",A1,1)),49),0))
Thanks C Masters, this is exactly what I needed!
General_Payne, your response was also helpful. I didn’t know about the Evaluate Formula option.
instead of using LEFT I would try something like this
=IF(MOD(A1,1)<0.25, (INT(A1)-1)+0.99, IF(MOD(A1,1)>0.75, INT(A1)+0.99, (INT(A1)+0.49)))
it uses the MOD function to check what comes after the decimal, and creates the resulting price based on whether it is less than 0.25, more than 0.75, or 0.25 to 0.75. In this setup a value of 3.25 would become 3.49.
hope that helps
June 8th, 2011 at 2:15 pm
Unfortunately the entire formula doesn’t show up. You need to put spaces every now and then otherwise Yahoo cuts it off and adds … at the end.
The first glaring issue I see is the fact that the >25 and >75 have to be outside of the right formula brackets so change it from:
AND(Right(A1,2>25), Right(A1,2<75)) to AND(Right(A1,2)>25, Right(A1,2)<75)
Also another neat feature of excel is the "Evaluate Formula" which steps you through each part of your formula allowing you to debug any errors. To do this click on the cell containing the formula then press Tools>Formula Auditing>Evaluate Formula (Excel 2003). I don’t know where it is on 2007 and 2010 though as I always get lost in the ribbon.
Thanks and if you post the amended formula (with spaces) I can lend further assistance.
References :
June 8th, 2011 at 2:27 pm
instead of using LEFT I would try something like this
=IF(MOD(A1,1)<0.25, (INT(A1)-1)+0.99, IF(MOD(A1,1)>0.75, INT(A1)+0.99, (INT(A1)+0.49)))
it uses the MOD function to check what comes after the decimal, and creates the resulting price based on whether it is less than 0.25, more than 0.75, or 0.25 to 0.75. In this setup a value of 3.25 would become 3.49.
hope that helps
References :