Jump to content

Excel Help !


tomcatonnet99

Recommended Posts

this is my last resort! sorry tried finding answers to this one but to no avail!

 

I'm trying to get EXCEL to stop calculating beyond the decimal points but it just won't! It sure displays numbers rounded off to so many decimal points...

 

In a bid to avert that I tried to Substitute( cell value e.g 4.21234556, ".", ".0000") which resulted in 4.000021234556

 

Now this should suffice for most calculations where I just need the units digit '4' into my calculation and not the rest!

 

but in some cases like '0.000021234556' it creates terrible errors taking into account all the digits after the decimal

 

 

How do I get it to STOP! Pls........ anyone?????????

 

I'm trying to subtract a given date from a start date = so many days

 

so many days / 14 = so many fortnights ( but I need for those fortnights to be without any decimal value so as to allow dates to be calculated for every WEDNESDAY Or Thursday etc ... Pays day:)) without having to worry about the number of fortnights in the month

 

cheers! in anticipation...:)

Link to comment
Share on other sites

I'm unclear as to whether you're trying to use integers in your calculations or in the displayed values. But either way you just need to use the maths functions that are available to you. There's a lot of useful stuff in Exel's own help files - find the maths functions section.

 

For example there's a ROUND function that rounds to the number of digits you specify, and an INT function that rounds down to the nearest integer. Have a look here ... http://office.microsoft.com/en-us/excel/CH062528291033.aspx

 

There are also many date and time functions that may be of use ... http://office.microsoft.com/en-us/excel/HP052042111033.aspx#Date and Time functions://http://office.microsoft.com/en-us/e... Time functions

Link to comment
Share on other sites

thanks, all sorted But will definitely try finding out more .....

 

I tried the substitute ....... as earlier which resulted in 3.214 being displayed as 3.0000214 and then used Round(3.0000214,0) which yeilded '3' just what I was after ....

 

There quite possibly is an easier way around this but lets see if anyone else has to offer any different to whats suggested

 

many thanks for your help Truly appreciate!!

 

cheers bud

Link to comment
Share on other sites

If it's just the display you're concerned about, then you can just change the cell properties (via right-click) to just display a number to zero decimal places. So the actual value might be '3.456' but you'll only see '3' displayed.

 

So using the ROUND fn would lose accuracy if you wanted to use the value in another calulation, 'cos it actually changes the cell value to '3'. Changing the cell properties keeps the value at '3.456' (although you only see '3') which is useful if you need that accuracy for future calculations.

Link to comment
Share on other sites

Yep I only require for it to accept the unit place calculation and not the decimal point ( coz I'm trying to get it to divide the number of days between dates by 14 to get the number of fortnights and arrive at only Wednesdays ... - this being achieved by the start date being a Wednesday..:)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.