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.
Bookmarks