Formula Help

[Tivpine Anande]

VoteBook Dummy.xls
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2	3,005,987.89
3
4												Treasury Book 46 (R)
5		S.W./A.I.E./R.I.E. Number:								NJC2/2/02/Vol.1/1/103
6		Amonut Available for Expenditure:								First Quarter Allocation		N	20,000,000.00
7										Second Quarter Allocation		N	25,000,000.00
8										Third Quarter Allocation		N
9										Fouth Quarter Allocation		N
10		LIABILITIES								Total Allocation		N
11
12	Incured (9)			Cleared (10)			Total Outstanding (11)			Remarks (12)		Balance Available (13)			Line No. (14)	Line No. (15)
13										First Quarter Allocation 2009		20,000,000	00
14
15	3,005,987	0					3,005,987	0		ABC Ltd		16,994,013	13
16
Road Motor Vehi.

Hi All,

I need help anyone, i have this figure 3,005,987.89 to be deducted from 20,000,000.00, i belive i should have 16,994,012.11.

This is my problem - i have this formula !=INT(R2) in cell R15 which is working perfectly, what i want to also do is have the the decimals subtract themselves and post to say Cell S15.

Also AC15 is derived this formula !=AC13-R15 which should also equal 16,994,012.11.


Thanks Guys

Tivpine

 

[Kelbo]

I suggest that you change the formula in R15 to:

=R2. This will maintain the decimal parts. Change the format of R15 to 'Number' with no decimal places.

This should solve both problems!

Kelbo

 

[Tivpine Anande]

Kelbo,

Thanks for your reply to my post, even if i did that i will just be repeating the decimals without subtracting them which is what i really want to do here?

Regards,

Tivpine

 

[Kelbo]

In cell S15 put =R2-R15 this will give the decimals.

Kelbo

 

[Tivpine Anande]

Kelbo,

Ok, not bad but how can i get it to give me just 89? This formula =RIGHT(R15,2) would give me 89 without 0. but that is if i have the 3,005,987.89 in one cell. Could you also please help me look at AC15, It is actually like this 20,000,000.00-3,005,987.89=16,994,012.11 again with =RIGHT(AC15,2) i would actually get only 11.

Tivpine

 

[jeffreybrown]

Try the MOD function...see what that gets you.

=MOD(R2,1)

 

[Tivpine Anande]

Jeff,

Didnt work it gave me 0.89, 89 alone will just be it.

Thanks,

Tivpine

 

[jeffreybrown]

Is this it...

=MID(SUBSTITUTE(MOD(R2,1),".",""),2,2)

 

[Tivpine Anande]

Jeff,

Thanksssssssssssssssssssssssssssssssssssssssssssssss it worked, Could you also please help me look at AC15, It is actually like this 20,000,000.00-3,005,987.89=16,994,012.11 again with =RIGHT(AC15,2) i would actually get only 11.

Tivpine

 

[jeffreybrown]

I am headed out the door right now for a couple of hours, but if nobody has stepped in by then I would be glad to look into upon my return. What is the result you are looking for in AC15.