Round Off (Up or Down)

R

Rashid Khan

Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
 
T

tina

When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
 
R

Rashid Khan

oops sorry,
The formula Int(......) is against GrandTotal.

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
 
R

Rashid Khan

RoundOff = TotalDue - SubTotal

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
 
R

Rashid Khan

Hi Tina,
I think I have mixed up everything .. I am giving herewith the actuals from
my Report
Subtotal = 480.43 --> the formula is [Quantity]*[UnitPrice]
Freight = 123.00 --> the control source is Freight field
Sales Tax = 24.02 --> the formula is ([SalesTaxRate]*[Subtotal]*100)/100
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Total Due = =Int([Subtotal]+[Freight]+[Sales Tax]+0.5)

I get 146.57 shown in the Round Off instead of -0.45. What can be the
problem?

TIA
Rashid

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
 
T

tina

Round Off = It should be -0.45 in this case --> the formula is [Total
Due] -
[Subtotal]
Click to expand...
i believe the formula should be [Total Due] - ([Subtotal]+[Freight]+[Sales
Tax])

hth


Rashid Khan said:
Hi Tina,
I think I have mixed up everything .. I am giving herewith the actuals from
my Report
Subtotal = 480.43 --> the formula is [Quantity]*[UnitPrice]
Freight = 123.00 --> the control source is Freight field
Sales Tax = 24.02 --> the formula is ([SalesTaxRate]*[Subtotal]*100)/100
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Total Due = =Int([Subtotal]+[Freight]+[Sales Tax]+0.5)

I get 146.57 shown in the Round Off instead of -0.45. What can be the
problem?

TIA
Rashid

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
Click to expand...

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Rashid Khan said:
Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3) GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
Click to expand...
 
R

Rashid Khan

Thanks Tina. It works perfectly. Really appreciate for your time and
advice.
Rashid
tina said:
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Click to expand...
i believe the formula should be [Total Due] - ([Subtotal]+[Freight]+[Sales
Tax])

hth


Rashid Khan said:
Hi Tina,
I think I have mixed up everything .. I am giving herewith the actuals from
my Report
Subtotal = 480.43 --> the formula is [Quantity]*[UnitPrice]
Freight = 123.00 --> the control source is Freight field
Sales Tax = 24.02 --> the formula is ([SalesTaxRate]*[Subtotal]*100)/100
Round Off = It should be -0.45 in this case --> the formula is [Total Due] -
[Subtotal]
Total Due = =Int([Subtotal]+[Freight]+[Sales Tax]+0.5)

I get 146.57 shown in the Round Off instead of -0.45. What can be the
problem?

TIA
Rashid

tina said:
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.

that can't be right, because the Int() function will only return a whole
number, not a decimal. seems you have left out part of the calculation in
the second scenario, or did you mistype your formula in the post? or both?


Hello All,
I am using Access 2002. I had the following problem:

Example No. 1
Line 1) SubTotal : 480.52
Line 2) RoundOff : + 0.48
Line 3) GrandTotal : 481.00

My problem is that if the SubTotal ends with 0.50 and greater then the
round off should be up to the next whole number and the value in No.2
RoundOff should show the amount by which it is rounded up which in this
case is +0.48 and rounded up to 481.00 (as shown in the Line 3)
GrandTotal).


Example No. 2
Line 1) SubTotal : 480.43
Line 2) RoundOff : -0.43
Line 3) GrandTotal : 480.00


The solution I was using
INT() function to calculate Line3 = int([line1]+0.50), and then calculate
Line2 = Line3 - Line1
I have added FreightCharge as follows:
Line 1) SubTotal :480.43
Line 2) FreightCharge : 123.00
Line 3) RoundOff : -0.43
Line 4) GrandTotal: 603.00
When I change the formula RoundOff=Int([Subtotal]+[FreightCharge]+[Sales
Tax]+0.5)
It shows RoundOff value as 122.57 instead of -0.43.
How can I achieve the correct RoundOff and GrandTotal figures?
Any help would be greatly appreciated
Rashid Khan
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Round Off Up or down formula 2
Calculate to the third decimal and round up or down in cell 6
Round up or down by set variable 3
Round up and down 2
Forcing numbers to round up or down 5

Top