Rounding the sum of a named range

[Yamezz]

I use Excel for invoicing products. I have just encountered a problem with rounding with a certain combination of products.

This is what my display looks like currently:

Quantity	Description	Account	Amount	WET	GST
6	2023 Sparkling Rosé	Direct Sales	$114.55	$18.27	$11.45
6	2024 Sparkling Vermentino	Direct Sales	$114.55	$18.27	$11.45
		Subtotal	$229.09
		WET (included)	$36.54
		GST	$22.90
		Total	$251.99

This is what I would like to display:

Quantity	Description	Account	Amount	WET	GST
6	2023 Sparkling Rosé	Direct Sales	$114.55	$18.27	$11.45
6	2024 Sparkling Vermentino	Direct Sales	$114.55	$18.27	$11.45
		Subtotal	$229.10
		WET (included)	$36.54
		GST	$22.90
		Total	$252.00

This is the actual data behind the displayed table

Quantity	Description	Account	Amount	WET	GST
6	2023 Sparkling Rosé	Direct Sales	114.5454545	18.27	11.45
6	2024 Sparkling Vermentino	Direct Sales	114.5454545	18.27	11.45
			=IF(A4<>"",IF(C4="Samples",0,A4*(VLOOKUP(SaleCustomer,'Love and Glory.xlsm'!TableCustomerDetails[#Data],9,FALSE))),"")
			=IF(A5<>"",IF(C5="Samples",0,A5*(VLOOKUP(SaleCustomer,'Love and Glory.xlsm'!TableCustomerDetails[#Data],9,FALSE))),"")
			=IF(A6<>"",IF(A6="Samples",0,A6*(VLOOKUP(SaleCustomer,'Love and Glory.xlsm'!TableCustomerDetails[#Data],9,FALSE))),"")
		Subtotal	229.0909091
		WET (included)	36.54
		GST	22.9
		Total	251.99

The values of 114.5454545 are the result of a formula, which is shown in the unused rows below. The values are so many decimal places because they are actually calculated from a retail price less 10% GST, in this case $126/1.1

When I invoice 12 bottles of one product, I get a Subtotal of $229.10, but when I invoice 6 bottles + 6 bottles, I get a Subtotal of $229.09. This is calculated with =SUM(SaleAmountColumn)

To get to the required $229.10 value I have tried both =SUM(ROUND(SaleAmountColumn,2)) and the individual cells =SUM(ROUND(D2,2),ROUND(D3,2),ROUND(D4,2),ROUND(D5,2),ROUND(D6,2)) but the formulas in the unused rows result in a #VALUE! error

How can I achieve a consistent $229.10 regardless of entering 1 lot of 12 bottles, or 2 lots of 6?

 

[Sam_D_Ben]

How about, I am not sure, but still please check:

Book1
	A	B	C
1	114.5454545		114.5454545
2	114.5454545		114.5454545
3	229.10		229.10
Sheet2

Cell Formulas
Range	Formula
A3	A3	=CEILING( SUBTOTAL(9,A1:A2), 0.01)
C3	C3	=ROUND(C1, 2) + ROUND(C2, 2)

 

[Alex Blakenburg]

I would round the original calculation.

=IF(A4<>"",IF(C2="Samples",Round(0,A2*(VLOOKUP(SaleCustomer,'Love and Glory.xlsm'!TableCustomerDetails[#Data],9,FALSE),2))),"")

 

[Yamezz]

I would round the original calculation.

=IF(A4<>"",IF(C2="Samples",Round(0,A2*(VLOOKUP(SaleCustomer,'Love and Glory.xlsm'!TableCustomerDetails[#Data],9,FALSE),2))),"")

Thanks Alex, that's obvious in hindsight!

@Sam_D_Ben Your answer works for the example I gave you, but I realised I also have occasions where I end up with $X.01, when it should be rounded down. Thanks for your input though.

 

[Alex Blakenburg]

Thanks for the feedback. Glad we could help.
PS: in terms of what you were trying initially this might work in Excel 2019

=SUMPRODUCT(ROUND(SaleAmountColumn,2))