Convertering All Numbers To Decimal

Feb 16, 2007

My coworker's excel program is converting all data to decimals. He enters in 2004 as a date and it makes it 2.004. I opened the same file on my computer and it works fine. I have done all fo the formatting to try and make it a number or a general field. The general field is not showing a preview as if there is some error overriding the whole program. He has some other numbers that he has already entered as currency and they work fine. I was able to do a =2004 and it showed as 2004 of course.

View 3 Replies


ADVERTISEMENT

Converting Decimal Numbers To Text With Dot Numbers

May 27, 2006

we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:

Sub ForceToNumber()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
With wSheet
. Range("IV65536") = vbNullString
.Range("IV65536").Copy
.UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End With
Next wSheet
End Sub

Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

View 3 Replies View Related

List Only Whole Numbers From Decimal Numbers

May 23, 2009

From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed

For example

34.95
21.88
21.00
56.00
45.77

Only those valaues ending in .00 will list. I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents

View 4 Replies View Related

Delete Decimal Numbers Only?

Dec 14, 2008

Is there a way to delete only numbers ending in a decimal from a column of numbers? I have a column of numbers but only the non decimals are relevant to the next equation.

View 3 Replies View Related

Counting Decimal Numbers

Oct 25, 2006

I am trying to find a way of counting decimal numbers if, say, they begin with 3.

For example, I might have 3.33, 4.1, 3.0, 5.65, 3.8, 3.7, 3.33, etc.

I want to count anything that begins with 3 (3.33, 3.0, etc). Using the data above the answer would be 5 ...5 numbers that begin with a 3.

If this possible?

There doesn't appear to be the facility with Countif. I don't want to truncate the data (the spreadsheet already is quite extensive). I have tried using =SUMPRODUCT((Y$2:Y$170 >=3)*(Y$2:Y$170<=3.9)) but this will return an answer only when there is a complete set of data within cells within the range.

View 10 Replies View Related

Spliting Numbers Regarding Decimal

Aug 8, 2008

I would have a preliminary data, a number, on sheet1. The final data would be on sheet2.

In the case where there is a whole number as the preliminary data, the data would remain the same as the final output. (eg. 248 --> 248)

In the case where there is a number with a decimal, I need a function where it looks at the original number as two sets of WHOLE numbers. (Eg. 248.30 --> 248 and 30). Where the digits to the left of the decimal remains the same and the digits to the right would be divided by x (in this case, 10). The final output would be 2483 (where 248 is the same and the 30 is divided by 10.

Since I am using an older version of excel, I cannot use quotient. I know that I would be using the vlookup function and perhaps an if function. however, I do not know how to separate the numbers in regards to the decimal.

View 12 Replies View Related

Whole Numbers Being Changed To Decimal

Apr 29, 2007

when I tried to input numbers into an Excel spreadsheet (Excel 2003) the numbers were entered as decimals. Example: I type in 235 with NO decimal point, Excel enters it as 2.35, even when starting from scratch, using a new sheet or new workbook. I tried using format cell from the menu bar to choose "general" then as "number". Nothing changed. I also tried to set the decimal places at "0" using the number format. All that did was truncate the number to 2 with the .35 not being displayed in the cell. It IS displayed in the formula window as 2.35... but not as I entered it... 235. This happens no matter whether or not it's a brand new blank workbook or worksheet

View 2 Replies View Related

Subtracting Time (in Numbers) To Decimal

Oct 21, 2009

How can I add/subtract time entered as 1830 to give a decimal value?

Start=1830
Finish=2000
Time between = 1.5

Also, how can I convert time as 1830 (24hr) to 12hr time? ie. 0630.

View 3 Replies View Related

Allow Only In Textbox1 Numbers & Decimal Point?

Jan 31, 2014

I need to allow only in textbox1 Numbers & decimal Point.

View 4 Replies View Related

Decimal Numbers To Feet And Inches

Jan 19, 2004

Is there a way to store a text style that will allow me to convert decimal numbers into feet and inches in the same cell?

I want to be able to type in a number like 3.5 and have it read 3'-6".

View 9 Replies View Related

Parse Decimal Numbers From Text

Nov 6, 2006

A while back I asked how to remove the letters from entries that have both numbers and letters. I as pointed to a UDF called "ExtractNumber".

Here is the code for that UDF: .....

View 9 Replies View Related

Compare Variable To Decimal Numbers

Sep 28, 2007

I have an If loop which looks like: If x < 54.5 And x > 52 Then

I have declared x as "Long", but still when it does the comparison it seems to round x up to the nearest whole number for the second comparison ( x > 52 ).

View 4 Replies View Related

Add X Decimal Places To Numbers But Not Zeros

May 24, 2008

I have a column of numbers with one decimal point and some blank entries too. I need to convert all the numbers (including the zeros, but not the blanks) to two decimal points. Any idea on how this can be done?

Examples: 0.1 needs to become 0.10, 0 needs to stay 0, a Blank cell needs to remain Blank.

View 9 Replies View Related

Validate TextBox For Decimal Numbers

Jun 6, 2008

I have set up a form which requires one textbox to have a decimal followed by four numbers (ex .5780) and another which requires two numbers, a decimal, then two more numbers (ex 57.80). how this can be accomplished? I am new to Visual Basic coding!

View 6 Replies View Related

Excel 2010 :: Sum And Average Of Decimal Numbers

Apr 10, 2014

Basic Excel-2010 functions.

I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.

1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error
2) when I use =AVERAGEA(A2:A7), it yield ans=0
3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;

View 4 Replies View Related

Changing The Decimal Places Without Rounding The Numbers

Mar 19, 2009

I was trying to decrease the decimal places of the data figures that I'm currently working on my report, however, it keeps rounding-up the decimal numbers. I wish to keep the original numbers and just decrease the decimal places.

For example:
The original figure is = 7260.12903225806
Upon decreasing the decimal figures to just 2, the result became = 7260.13

Is it possible for me to just have this result = 7260.12 instead of 7260.13?

I've tried using the TRUNC formula butit does not work if the 2nd decimal value is 0.

For example:
The original figure is = 227161.905808985
Upon applying the formula, the result became = 227161.9 instead of 227161.90

View 4 Replies View Related

Remove The Display Of The Leading Zero In Decimal Numbers

May 15, 2009

Is it possible to remove the display of the leading zero in decimal numbers of less than one in Excel 2007?

View 3 Replies View Related

Code Writes Decimal Numbers Next To Each Other Instead Of Adding

Sep 4, 2009

Code writes decimal numbers next to each other instead of adding. I have the following code on a very simple form:

View 5 Replies View Related

Require 2 Numbers Before A Decimal In Data Validation?

Feb 16, 2012

How can I require 2 numbers before a decimal in data validation? I have a formula to require at least to characters after.

I'd like to require the user to enter at least ##.##

View 9 Replies View Related

Filter Only Decimal Numbers - Result In Same Column

Apr 3, 2012

I have few numbers in Column "A". Few are normal figures & few are decimal numbers, like this:

20
5
5.63
4.5
200
53.263
125.5

Now I want to filter only the decimal numbers, so that after filter the result will show like this:

5.63
4.5
53.263
125.5

But I don't want to use any other column for doing so. I can do it using MOD in other column but I want the result in the same column.

View 7 Replies View Related

How To Convert Big Hexadecimal Numbers To Decimal Format

Mar 19, 2013

I need to be able to convert big hex numbers into decimal numbers. The problem that I have with this is that when you put a hex number that includes letters excel considers it text. And if the number is too big, you can't convert it to decimal format, and when you try to, it comes up with the #NUM! error.

Number: "78652385098375A879FF747C9847A00" without quotes

View 4 Replies View Related

Converting Decimal Numbers To Minutes And Seconds

Jul 27, 2009

I have an excel sheet with a row of numbers example below. These numbers represent the length of time that a telephone call was. The problem is they are decimal numbers (units of 100) but they represent seconds and minutes of a phone call which change up one number when the seconds hit 60 ...

View 9 Replies View Related

Reducing Decimal Points Without Rounding Up The Numbers

Jul 25, 2006

I have columns of geological data in number form which may have about 4 or 5 decimal points. I want to reduce them to 2 decimal points without rounding the numbers up. Is there a simple way to do this?

View 4 Replies View Related

Change Default Decimal Points For Numbers

Feb 14, 2008

I've got a wierd simple problem in Excel 2003. I have a laptop and a desktop machine, and I'm an accountant who uses the fixed decimal feature as a default, with it set to 2 decimal places.

On the desktop machine, if I input "23." into a cell and then hit enter, the value left in the cell is "23.00" However, on the laptop, when I do the same thing, I get "0.23" In essence, it ignores the fact that I entered a decimal point.

If I enter "23.0" in a sheet on the laptop, it puts "23.00" in the cell.

I thought maybe it was some sort of hardware thing, like the KB was messed up, but then I used remote desktop to try to see what would happen if I enter numbers into the other computer using the laptop, and into the laptop using the desktop. The laptop when connected to the desktop performed normally, just like the desktop machine. The desktop, when connected remotely to the laptop behved like the laptop machine. In other words, I believe this test takes Hardware out of the picture.

Which leaves some strange obsure setting in excel someplace tht is causing this behavior, and I can't seem to find it.

Does anyone here know why these two installs treat the data entry so differently? It's driving me nuts.

View 9 Replies View Related

Convert Decimal Numbers (Minutes) To Time Format

Mar 8, 2013

How to convert decimal numbers which represent minutes into time format? Like this

Sheet3  GH1MinutesTime212.9729729700:13:00325.9459459500:26:004116.756756801:57:00

View 2 Replies View Related

Forcing Decimal Places (wanting NUMBERS Not TEXT)

Jun 4, 2008

I am wondering if there is any way to "force" a number to have three decimal places instead of two and still have that number valued as a number for use in formulas later (like AVERAGE, etc.).

What I have is a combination of HLOOKUPs, IF statements, and Conditional Formatting.

I have a Master Grades sheet and individual Class sheets. I use an HLOOKUP, matching a Student Number in both sheets, and looking in the Class sheet for the P/F indicator. If the indicator is a F, the HLOOKUP is to return the Final Grade from the Class sheet and input that into the Master Grades sheet, forced to three decimal places (whereas the numbers in the Class sheets are at two decimal places) If the indicator is P, the HLOOKUP simply returns the Final Grade from the Class sheet and inputs it into the Master Grades sheet with two decimal places.

I have the failure grades forced to three decimal places so that the Conditional Formatting in the Master Grades sheet highlights the failed grades for teachers and anyone else can see right away that a student failed, but the number still allows the Master Grades sheet to calculate the overall AVERAGE for the student as well as the AVERAGE for that class.

If any of this is not quite clear, please ask and I can further expand on what I am doing.

Here is the code I have right now that is not working correctly (and I know why it's not): ....

View 9 Replies View Related

Truncate Range Of Numbers Where Number Will Not Show More Than 2 Past The Decimal

Oct 21, 2013

How do I truncate a range of numbers where a number will not show more than 2 past the decimal ....and this be in vba as part of a macro.

View 3 Replies View Related

Decimal Places For Close Ties Of Numbers Based On Cell Value

Apr 15, 2008

I have a pivot table of values displaying agency's performance based on their sales percentage to target. When the race is 'loose', and all companies are 1 or more percentage point away from each other, I format the values without decimal places. If two agencies appear tied, I add a decimal place to seperate them. Occasionally the race is really tight and I have to increase the format to two decimal places to seperate them. how to do this automatically?

View 4 Replies View Related

Paste Two Decimal Number In Excel Without Extra Decimal Places Appearing

Aug 13, 2009

I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...

View 2 Replies View Related

Formatting Group Of Cells To Have Different Decimal Rules Based On Decimal Value

Aug 3, 2006

Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved