Decimal Function

[MR-77]

Dear All,
First of all my English Language not good but I hope all will be understand. Thers is anyway someone can help me please, I need to know what is the function if I want to calculate decimal only, for example:

56.333
2.378
129.238
14.313
134.566
0.988
0.004
0.212
Total is: 338.032

I want total in two cells (338 in one cell and 032 in separte cell) like this:
338
032
I used this function: ==INT(SUM(A1:A8)) it gives me 338
but what is the other function I can use to calculate only decimal?

For your kind information, I used Excel 2007
Please for quick help.

Thank.

Best Regards,

Mostafa Saqallah
email: m_saqallah@hotmail.com

[martindwilson]

=mod(sum(a1:a8),1)

[MR-77]

Thank you Dear,

I appriciated your kind help. I used the function which you gave it to me
=mod(sum(a1:a8),1)
The result is 0.032 it's correct

But how can to be make the result without decimal automatically, I mean like this: 032

Please for quick help.

Thank.

Best Regards,

Mostafa Saqallah
email: m_saqallah@hotmail.com

[DonkeyOte]

see the INT function ... if you wish to format as 032 either

a) use a Custom Number Format applied to the cell of 000

or

b) encase the INT function within a TEXT function though note the result will be a text string rather than a number.


On an aside - unless you love SPAM remove your email address from your posts.

[MR-77]

Thank you again.
But it's not working with me. Could you please write the exact function for me?
Sorry for disturing you again.

Please for quick help.

Thank you.

Best Regards,

Mostafa Saqallah

[martindwilson]

you could use
=MID(A1,FIND(".",A1)+1,5) to get the last bit after decimal but it would be text
or even
=MID(SUM(A1:A5),FIND(".",SUM(A1:A5))+1,5)

[DonkeyOte]

Sorry, my bad, I misread.

[MR-77]

Dear Martindwilson,
Really many thanks for your kind help I appriciated that.
From this function I can save my time in my work.

Again thanks a lot for your Mr Martindwilson

Best Regards,

Mostafa Saqallah

[MR-77]

Dear All,

Please be informed that I am a normal user of Excel software, in an Excel spreadsheet I would like to place a picture behind the text. I mean when I like to write anything in Excel coming infrom the picture same as Winword. Could you please guide me with the steps, if it could be done.
Kindly note, I used Excel 2007

Pleae help me as soon as possible

Best Regards
Mostafa Saqallah

[martindwilson]

Mostafa this is an entirely separate question please start a new thread.

[MR-77]

Sorry for my small mistakle Martindwilson

[MR-77]

Dear All,
Sorry again for disturbing you, but again I faced problem in the function. For example:
62.500
62.500
26.738
26.350
62.500
62.500
Total is: 303.088

I used this function in seperate cell: =INT(SUM(A1:A6)) it gives me 303 (It's correct)
But when I used this function in other cell: =MID(SUM(A1:A6),FIND(".",SUM(A1:A6))+1,5) It gives me (#VALUE)

Could you please give the correct function I can used in two cells (303 in one cell and 088 in separte cell) like this:
303
088

Please for quick help.

Thank.

Best Regards,

Mostafa Saqallah

[DonkeyOte]

If you're interested in 3 decimals then either

=TEXT(MOD(SUM(A1:A6),1)*1000,"000")

or

=REPLACE(TEXT(MOD(SUM(A1:A6),1),".000"),1,1,"")

both of the above will return text strings in format of ### ... I am assuming that is your preference
(ie not a Custom Format)

[MR-77]

Thanks a lot dear.
You are I should used 3 decimals in Kuwait, both way is right & correct. But when I used it gives me 087 but the right asnwer is 088

I know I'm disturbing you but I do my best to finalize this problem.

Thank you again

Waiting your answer.

[DonkeyOte]

Post a sample.

[MR-77]

For example, all my number which I used with 3 decimals as below:
62.500
62.500
26.738
26.350
62.500
62.500
Total is: 303.088

I used this function in seperate cell: =INT(SUM(A1:A6)) it gives me 303 (It's correct)
Now for the function of decimals I used these functions which you gave it to me:
=TEXT(MOD(SUM(A1:A6),1)*1000,"000")
or
=REPLACE(TEXT(MOD(SUM(A1:A6),1),".000"),1,1,"")
but it gives me this result: 087 (not correct) the right result is: 088

Please for quick help.

Thank.

Best Regards,

Mostafa Saqallah

[MR-77]

Always I used number with 3 decimals, for example:
62.500
62.500
26.738
26.350
62.500
62.500
Total is: 303.088

I used this function in seperate cell: =INT(SUM(A1:A6)) it gives me 303 (It's correct)
Then I used your functions which you gave it to me in other cell to show me only decimals:
=TEXT(MOD(SUM(A1:A6),1)*1000,"000")
or
=REPLACE(TEXT(MOD(SUM(A1:A6),1),".000"),1,1,"")
The result is: 087 (not correct), the right result should be: 088

Please for quick help.

Thank.

Best Regards,

Mostafa Saqallah

[DonkeyOte]

As I said - post a sample - ie a file.

Those specific values would generate 088 therefore I suspect the cells (A1:A6) are in fact simply formatted to 3 decimal places.
As a result of which you are experiencing roundings, however, to verify to be the case we need to see a file containing the exact values you're summing.

To elaborate - a cell containing the value 62.49999999 formatted to 3 decimals will appear as 62.500 ... however when summed it is the underlying value that is used and not the formatted value*

The below

Please Login or Register  to view this content.

the values to the left would generate 087 for ex. even though the formatted values (right) would appear to total 088

If this is the issue we can account for it - but we need to see the file to be sure.

*unless using Precision as Displayed - not generally recommended