Rounding problems using lookup instead of rounding functions

Computed in binary floating point, 10/.77 = 13.9999999999999, not 14.

I suggest you calculate the price in a cell rounded to the nearest penny:

=round(10.78/.77, 2)

... then apply the prior custom rounding formula.

I was able to insert the round function into the formula instead of messing around with adding more cells and it seems to work now. This is the finished (I hope)formula that I can apply to a vendor's price sheet after filtering and sorting to help keep our prices and margins consistent. (G being our cost, $H being the qty of items/case, $I is misc additions and shipping, and $G being the particular margin/item. thank you very much again!!!! Now I just have to convince my aging boss that doing all this stuff by hand on paper is the reason she is losing margins, and the time saved having the computer do this can better go towards customer service. That will be the hard part.


=INT((((G2/data!$H$2)+data!$I$2)/(1-data!$G$2)))+IF((((G2/data!$H$2)+data!$I$2)/(1-data!$G$2))<10,LOOKUP(MOD(ROUND((((G2/data!$H$2)+data!$I$2)/(1-data!$G$2)),2),1)+0.001,{0,30,50,70,80,90}%,{29,49,69,79,89,99}%),IF(AND((((G2/data!$H$2)+data!$I$2)/(1-data!$G$2))>=10,(((G2/data!$H$2)+data!$I$2)/(1-data!$G$2))<15),LOOKUP(MOD(ROUND((((G2/data!$H$2)+data!$I$2)/(1-data!$G$2)),2),1)+0.001,{0,50}%,{49,99}%),LOOKUP(MOD(ROUND((((G2/data!$H$2)+data!$I$2)/(1-data!$G$2)),2),1)+0.001,{0,25}%,{-1,99}%)))

You're welcome.

I wouldn't do it that way, and I don't think a formula like that is a selling point to your 'aging boss,' but if you're happy with it, great.

really? maybe I'd have to show you the whole spreadsheet to see how I'm using it. I'm trying to use the same formula for lots and lots of items that vary by margin, additional costs, qty/case etc...I download a list of items and costs from a vendor, filter and sort everything according to serial numbers, then copy and paste the formulas into a table that pulls the margins and other things from another worksheet. It seems to me that the fewer cells I have to use and reference, the better. Anyway...unless you can look at the entire spread sheet, I don't think I could really ask for help with simplifying, but I think it's about as simple as I can make it. Can you think of a problem that could be caused by incorporating the round function like I did? I think it saves me a lot of work to do it this way. The spreadsheet is to large to attach (9mb) so I might be out of luck with that kind of advice

You calculate the same value six times in that formula. Take pity on the guy that needs to try to figure out why someday.

There's a price calculation and a rounding calculation, two simple and distinct aspects that are now rolled up in one needlessly complex formula. There are 16,000 columns on a worksheet; don't be afraid to use a few of them to expose the design.

You're welcome.