Subtract values from 2 different cells

[jackt05]

Hi all,

I have a simple chart of weight like below:
<!-- Please do not remove this header -->

Received Rice	Used Rice	Remaining Rice
KG	Gram	KG	Gram	KG	Grams
500	200	200	300

<tbody>
</tbody>

The problem is that when I minus used Rice KG from Received Rice KG
it shows the answer as KG 300 & -100 Grams in The Remaining Rice cells,
But the answer should be KG 200 & 900 Grams.

How to use formula to get the right ans or is there any other method
of creating the table but I need to have different cells for KG and Grams.

Thanks for help

 

[AlexaS]

Hi all,

I have a simple chart of weight like below:<!-- Please do not remove this header -->

Received Rice	Used Rice	Remaining Rice
KG	Gram	KG	Gram	KG	Grams
500	200	200	300

<TBODY>
</TBODY>

The problem is that when I minus used Rice KG from Received Rice KG
it shows the answer as KG 300 & -100 Grams in The Remaining Rice cells,
But the answer should be KG 200 & 900 Grams.

How to use formula to get the right ans or is there any other method
of creating the table but I need to have different cells for KG and Grams.

Thanks for help

How are the answers supposed to be 200KG and 900 Grams? The only way I could possibly see those being the answers is if you have a previous column that is Beginning Rice, which would then add Received and subtract used.

 

[jackt05]

I need to minus 2.3 kg from 5.2 kg where values
of KG and Grams are in different cells

So my answer is as follows
In cell of KG I put =500-200
and in cell of Gram I put =200-300

Received Rice	Used Rice	Remaining Rice
KG	Grams	KG	Grams	KG	Grams
500	200	200	300	300	-100

<tbody>
</tbody>

I want it to be

Received Rice	Used Rice	Remaining Rice
KG	Grams	KG	Grams	KG	Grams
500	200	200	300	200	900

<tbody>
</tbody>

as this is the right answer .

 

[sanrv1f]

The right answer is 299 KG & 900 Grams

And a way to get it is

Excel Workbook
	A	B	C	D	E	F
12	Received Rice		Used Rice		Remaining Rice
13	KG	Grams	KG	Grams	KG	Grams
14	500	200	200	300	299	900
Sheet1

Excel 2010

Cell Formulas
Range	Formula
E14	=INT(A14+B14/1000-(C14+D14/1000))
F14	=MOD(A14+B14/1000-(C14+D14/1000),1)*1000

 

[AlexaS]

Ok, so these formulas are really not pretty, but it will get you the correct answer. Although I would point out that if you have KGs (i.e. 5.2), they should really be a single digit, not put as 500. Otherwise your grams should really be 5,200.

Remaining Rice KG: =TRUNC((((A1*1000)+B1)-((C1*1000)+D1))/1000)
Remaining Rice Grams: =(((((A1*1000)+B1)-((C1*1000)+D1))/1000)-(TRUNC((((A1*1000)+B1)-((C1*1000)+D1))/1000)))*1000

These will get your answers as KG 2 and Grams 900

If you want the KGs to show as 200 and not 2, add *100 to the end of the first formula as shown: =TRUNC((((A1*1000)+B1)-((C1*1000)+D1))/1000)*100

 

[jackt05]

Thanks a Lot Shankar

 

[jackt05]

Thanks a lot Alex but as the entry asked has to be 500 instead of 5.
but I got your point
Thanks again

 

[AlexaS]

Ok, again not pretty for KGs: =TRUNC((((A1/100)+(B1/1000)-((C1/100)+(D1/1000)))))*100

And the one for grams seems to still be working (don't ask me how)

 

[jackt05]

Ok, again not pretty for KGs: =TRUNC((((A1/100)+(B1/1000)-((C1/100)+(D1/1000)))))*100

And the one for grams seems to still be working (don't ask me how)

Thanks Alex