prices not rounded to 2 decimal points

**philiasfogg** · 03-05-2009, 11:11 AM

I have the attached workup which is used to help prepare estimates for customers.

It works well, however i have found that the prices are not always accurate.

I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty.

I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points.
Also dont know how to integrate it into existing formulas and macros

**shg** · 03-05-2009, 11:20 AM

Take all the current formulas that compute an item cost or price, and wrap them like this:

=ROUND(formula, 2)

E.g., = ROUND(A1*B1, 2)

**philiasfogg** · 03-05-2009, 11:31 AM

Thanks

I tried that and it seems to work but I get the following error

#VALUE!

Is there a way I can do this without getting the error

**shg** · 03-05-2009, 11:33 AM

I dunno. What's the formula?

**philiasfogg** · 03-05-2009, 11:52 AM

the spreadsheet is above and it is on the workings tab

but an example formula is

HTML Code:

**shg** · 03-05-2009, 11:57 AM

You shouldn't need it in that formula, because the prices in the price list should already be rounded to two digits.

But the reason you get a value error is that you can't round a null string. So if you were going to round, you have to do it on the part that returns a numeric value, e.g.,

=IF($C17="", "", ROUND(VLOOKUP($C17,'price list'!$B:$F,COLUMNS($A$1:C$1), FALSE), 2) )