Dividing proportionally over quantity

[Eirohl]

Hi,

it is my first time to post here and I have a basic knowledge on excel formula thats why I have difficult time to create a exact solution for this.
Before I am computing manually and it cost me a lot of time and I try it on excel but cant create a formula that I needed. I am using this for my day to day leaving thats why I am eager to know a solution.
Here is my sample question
I have an amount and I needed to divide it to quantity but I need an answer that is around that amount but excel rounded the answer I cant rounded it I need an exact amount. Please see the attached file for more details what I talking about.

Ps. English is not my first language thats why I am having difficult time to explain what I am looking for but I hope someone can understand. I hope someone can help me to solve this. Thank you in advance.

 

[Joe4]

Welcome to the Board!

If things are rounding, it is probably because of your formulas. Can you post the formulas you are using here?
If you want them to match exactly, then you do not want to round or truncate the column which is determing the factor.

 

[Eirohl]

Hi, Thank you for reading this.
about the formula it is the basic =amount/liters the answer keep rounding thats why I try to trunc it. I need an answer that is not over on the said amount. I do trial and error if this rounded answer is not over or this unrounded is in the amount.

Welcome to the Board!

If things are rounding, it is probably because of your formulas. Can you post the formulas you are using here?
If you want them to match exactly, then you do not want to round or truncate the column which is determing the factor.

 

[Dave Patton]

|Review the following
If this doesn't help, please advise what you require.

T202107a.xlsm
	A	B	C	D	E
2	liters	Amount			Difference
3	18,000.00	20,430.79	1.13504389	20,430.79	0.00
4	18,000.00	20,430.79	1.135	20,430.00	-0.79
5
5e

Cell Formulas
Range	Formula
C3	C3	=B3/A3
D3:D4	D3	=C3*A3
E3:E4	E3	=D3-B3
C4	C4	=ROUND(B4/A4,3)

 

[Joe4]

about the formula it is the basic =amount/liters the answer keep rounding thats why I try to trunc it.

I think you may be confusing what is displayed with what is actually stored in Excel.
Changing the number of decimal places shown on the sheet in formatting does NOT change the actual value stored or used in Calculations.
You can change the number of decimal places shown via Cell Formatting, if you want.

The only way the calculations will be off that much is if you use the ROUND or TRUNC functions in your calculation, which you shouldn't do if you don't want the differences.

 

[Eirohl]

|Review the following
If this doesn't help, please advise what you require.

T202107a.xlsm
	A	B	C	D	E
2	liters	Amount			Difference
3	18,000.00	20,430.79	1.13504389	20,430.79	0.00
4	18,000.00	20,430.79	1.135	20,430.00	-0.79
5
5e

Cell Formulas
Range	Formula
C3	C3	=B3/A3
D3:D4	D3	=C3*A3
E3:E4	E3	=D3-B3
C4	C4	=ROUND(B4/A4,3)

I am sorry I forgot to inform that 2 decimal places that I need, even-though it is not exactly the amount as long as the answer is in the amount range. Thank you

 

[Joe4]

I am sorry I forgot to inform that 2 decimal places that I need, even-though it is not exactly the amount as long as the answer is in the amount range. Thank you

Just format your cell to only show two decimal places.
Then it will just show two, but really retain the complete value for calculations, so you won't get those differences that you don't want to see.

If you insist of rounding the factor amount using the ROUND or TRUNC functions, you WILL get a difference in your next calculation.