Help getting rid of decimal places

[DublinMike]

Originally posted this in the General Section think it belongs here:

Help getting rid of decimal places

Hi, newbie here, not very proficient in Excel, self taught from internet, so please bear this in mind when replying, many thanks, Mike.

Using Excel 2010

Workbook with 21 sheets, first sheet is the summary sheet (named SUMMARY) taking all it's content from the other sheets, named from 1 to 20, which are all laid out the same, but contain different data.

My problem is that I want to get rid of decimal places that are being brought into the SUMMARY sheet

In sheets named 1 to 20, I am using the following formula in H23 to arrive at a figure =IFERROR(100/(B5/B23),0) in H23

B5 and B23 are always whole numbers on all sheets 1-20, result can be whole or contain decimal places which I round up in the cell to show no decimal places

On sheet 1, B5 is 200 and B23 is 5, giving 2.50 as the answer, which is right, I have H23 set as a number cell with no decimal places so it rounds up to 3 which is what appears in the H23 cell.

All of the above is correct and appearing as I want it on the sheet, the problem arises when I import into the SUMMARY sheet as follows:

On the SUMMARY sheet in cell B59 I want the following to appear 5 - 3 but am getting 5 - 2.5 using the following formula

='1'!B23&" - "&'1'!H23

The first part is fine as B23 will always be a whole number

How can I stop it from bringing in the 2.5 and get it to bring in the rounded up 3 into cell B59

I have cell B59 set as a number cell with no decimal places but it still appears as 2.5

Also when I correct it as it appears a lot on the summary sheet, how do I get the following to happen

='1'!$B$23&" - "&'1'!$H$23

if I fill the above across columns how to I get the two '1' s in the formula to become '2' then '3' as I fill across

Should you reply please remember I am a dummy and know nothing about functions and I would be grateful if you reply in a simple manner

Many thanks for any help you can give me.

I got a reply but didn't work

using ='1'!B23&" - "&int('1'!H23)

Hi JieJenn, thanks for replying, tried it and it gets rid of the decimal places, but it rounds down or effectively just takes the first two numbers as in, the example I give above, the 2.5 becomes 2 when I use INT, I tried it on another cell where the result is 14.63 which appears on the sheet rounded up as 15 but when using INT it brings it into the summary sheet as 14 instead of the rounded up 15 that I want. Thanks again.

[Melvinrobb]

Replace the formula in b9 with :

Please Login or Register  to view this content.

Also, you would have to manually change the sheet number the formula is referencing.

[etaf]

will round() work for you

=ROUND(h23,0)

[Jonmo1]

Try using the round function on your formula.

The basic problem is that when you apply a number format to a cell, and have it show 0 decimal places.
That is only DISPLAYING the rounded value.
The actual complete value with decimals still exists in the cell.
And formulas that are referring to that cell see the actual value, not the value displayed by the cell's format.

So for this formula, use
=IFERROR(ROUND(100/(B5/B23),0),0)

This will make the ACTUAL value rounded as if it was the cell's formatting doing the round.

[DublinMike]

Many, many thanks, Melvinrobb, etaf and Jonmo1, I used your formula Jonmo1, worked a treat, thanks again all.

May I ask you about my second problem as given above, how do I get the two 1's in the formula ='1'!$B$23&" - "&'1'!$H$23 to increment automatically to become two 2's , 3's etc as I fill it across the rows in my sheet. if I fill it across using this formula the 1's stay as 1's all thw way across. thanks.