# Formula Help

#### Tivpine Anande

##### New Member
VoteBook Dummy.xls
1
23,005,987.89
3
4Treasury Book 46 (R)
5S.W./A.I.E./R.I.E. Number:NJC2/2/02/Vol.1/1/103
6Amonut Available for Expenditure:First Quarter AllocationN20,000,000.00
7Second Quarter AllocationN25,000,000.00
8Third Quarter AllocationN
9Fouth Quarter AllocationN
10LIABILITIESTotal AllocationN
11
12Incured (9)Cleared (10)Total Outstanding (11)Remarks (12)Balance Available (13)Line No. (14)Line No. (15)
13   First Quarter Allocation 200920,000,00000
14
153,005,98703,005,9870ABC Ltd16,994,01313
16

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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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

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

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

Kelbo

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

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

=MOD(R2,1)

Jeff,

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

Thanks,

Tivpine

Is this it...

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

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

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.

Replies
6
Views
152
Replies
4
Views
891

1,207,111
Messages
6,076,620
Members
446,216
Latest member
BEEALTAIR

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back