Numbers Changing To Decimals

Apr 4, 2008

I have received an excel file from an external source. Every time I change a number in a cell it reverts to a 2 decimal number. eg. I type in 8710 and it converts the value to 87.10. I have looked at the number formats, cleared the numbers format but I keep getting the same result. I have also e-mailed the file to someone else and everything is ok on their computer. Is there a property in the excel program that I need to change?

View 3 Replies


ADVERTISEMENT

Sort Mixture Of Numbers And Text Using Numbers And Decimals

Nov 13, 2013

I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)

Code:

Col A Col B
1Billing Accuracy
2Billing Time
3Credit Check Accuracy
4Credit Check Time
2.1Bill preparation

[Code]....

This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.

View 9 Replies View Related

Numbers Have Too Many Decimals

Feb 8, 2009

A spreadsheet created by exporting from QuickBooks as a .IIF file is opened in Excel 2003.

A macro multiplies a cell value and returns 1.77999997138977 rather than 1.78. The 1.78 is required for importing back into Quickbooks.

Part of the code is: ...

View 13 Replies View Related

Recognising 1 Or Decimals As Numbers

Dec 10, 2007

I have a weird problem with Excel. It recognises all numbers as numbers but excluding the number 1. It is only recognised as text as well as a decimals, for example 3.4. So every time I try and add these values up it completely ignores 1 and decimals.

Have I modified a setting?

View 10 Replies View Related

Whole Numbers To Decimals & Calculate

Jul 2, 2007

I want to convert the number in a cell, G7, from a whole number into a decimal and then divide that decimal into a whole number in cell E7 and give me the quotient in cell E8.

ie:312 = 6.0
286 = 5.5
260 = 5.0
234 = 4.5
208 = 4.0
182 = 3.5
156 = 3.0
130 = 2.5
104 = 2.0
78 = 1.5
52 = 1.0

Could this also be done with the entire range of numbers from 312 to 52 and giving decimals of 6.0, 5.9, 5.8 etc.

View 5 Replies View Related

Numbers Are Converting To Decimals

Dec 6, 2007

I received a complaint from one of my colleague that he is facing sudden problem with Ms-Excel(2002)
The problem is When he feeds number 2 in to a cell it automatically converts in to 0.02. I tried using "Decrease decimal" and "format cells" option and even through uninstalling office and reinstalling a different version but the problem still continues.

View 2 Replies View Related

Lookup Whole Numbers From Decimals

Aug 8, 2008

I 'm not sure why my custom function "minimize" is not working... I tried to do this with one of excels built in functions and would prefer a solution that way, but had to go the VBA route in the mean time. x and theta are paired together and I'm trying to reduce a select number of values x by their corresponding ratio.s It's corresponding ratio is determined by what degree value theta corresponds to. I wanted to do an if statement, but got confused...

=if((B2>=$J2)*(B2<$J3)) , A2*K2 , if((B2>=$J3)*(B2<$J4) , A2*$K3 , if((B3>=$J4)*(B3<$J5) , A3*$K4, .... etc. etc. etc........................

View 2 Replies View Related

Convert Numbers With Decimals To Percentage

Nov 11, 2008

I have a spread sheet with over 200 numbers like 3.3, 4.5, 6.6 and so on. Is there and easy way to convert them to Percentages?

View 2 Replies View Related

Cell With Name, But Want To Remove Everything Else, Numbers, Decimals Etc.

Feb 25, 2009

I'm trying to clean up a very large list of last names. Only one individual cell, but that cell includes numbers, decimal points, and spaces inbetween the numbers. All I want left in the cell is the last name. I have just under 100,000 to do! How would I go about this? Using Excel 2007.....

View 2 Replies View Related

Converting Hours To Numbers, Decimals

Nov 10, 2009

How do I convert hours into numbers and/or decimals?

Example:
Column A Column B
---------- -----------
30:05:00 to 30.05
26:10:00 to 26.10
262:47:00 to 262.47

Figures under Column B refer to info that I would like to get.

View 4 Replies View Related

What Numbers Have No Decimals In About 500-1000 Cells

Mar 17, 2009

I use a worksheet full of formulas to know what divisors a number have, but i need to see what numbers have no decimals in about 500-1000 cells. Is there a formula whitch computer can use to see if it shows a number or not (if a number have or dont have decimals)?

View 2 Replies View Related

Formula To Delete Whole Numbers But Leave Decimals?

Apr 22, 2014

I've got a spreadsheet that's basically a large list of numbers, both whole and decimal. For example, let's say this is in cells A1-A5:

4
0.65
1.34
3
8.2

Is there a formula to get rid of all of the whole numbers but leave the decimals? (What I mean by that is I don't need 4 or 3 as they're whole, but I need the decimals to be left alone).

I know it's probably a really awkward question but I have over 2,000 lines to go through, it will take a long time to do manually.

Perhaps if it's not possible to a formula to delete entries, maybe just make all whole numbers say something like "NO", so that I can sort the column in A-Z order and delete all of the 'NO's quickly by highlighting them all together.

View 5 Replies View Related

Cell Format: Only Numbers (no Date) With Any Amount Of Decimals

Sep 8, 2009

I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).

I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".

I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.

View 7 Replies View Related

Excel 2003 :: Concatenate Formula With Numbers With Decimals

Jul 12, 2012

I'm using excel 2003 and have a problem regarding some code.

Dit(a, b) = "=" & Hit(a, (d - 12 - e) + f) & "/" & (Pro & ".NrE.sol")

Where "Hit(a, (d - 12 - e) + f)" can be numbers with decimals.

When I run the code the result is nothing, unless the number is a number without decimals.

If I use just "Dit(a, b) = Hit(a, (d - 12 - e) + f)" it shows the right number.

View 2 Replies View Related

Convert Degrees Minutes & Decimals Of A Minute (12° 34.567') To Degrees And Decimals Of A Degree (12.57611°)

Oct 29, 2008

I am trying to convert Degrees Minutes & Decimals of a minute (12° 34.567') to Degrees and decimals of a degree (12.57611°). I have the formula to convert latitude, that is two digits, but it doesn't want to work with longitudes, three digits. (see attachment). This is the formula that I am using:

=IF(A2="","",(INT((LEFT(A2,3)+MID(A2,4,6)/60)*100000)/100000)&"d")

View 2 Replies View Related

Changing Numbers All By Itself

Jan 23, 2013

I have a simple spreadsheet for keeping track of gas mileage which I have been using for over two years with no problems. I have five columns A through E and right now I'm on row 72, that's where the problem started. When I enter the starting mileage in A and tab to B it changes the mileage number to a date and as my mileage number I type in changes the date also changes as I tab to B. See the sample below.

A B C D E
15357155968.491 28.14745024239
9/12/19420:00 10.44423.36269628244

The part that puzzles me is it does it in row 72, 73, 74, 75, and 76 then it goes back to working normal, and even though it puts the date in A and zeros in B it does the math right. I looked at column A and B for those rows and there isn't any formulas in them.

I Have a formula in column D =(B72-A72)/C72 and column E is just =B72-A72. Other then that the only other formulas I have is in column B row 177 it gives me total miles, in 178 it gives me total gallons, and in 179 it gives me average MPG.

View 3 Replies View Related

Changing Numbers With Errors: #div/o

Nov 16, 2009

When I run a report, for some reason our system imports certain numbers which have errors, as a consequence when I try and do a pivot table I get a lot of returns showing #div/o!. If you look at the spreadsheet the errors start on line 103 sheet 2 and finish on 2631. Sometime the report can be huge and takes a while to go through these manually to convert to numbers.

View 3 Replies View Related

Changing Formulas To Numbers?

Jul 25, 2013

I have a large spread sheet with about 10 tabs. Is there any way to convert all the formulas to numbers in just one macro.

View 3 Replies View Related

Selecting From A Changing Column Of Numbers?

Jun 7, 2014

I am creating a spreadsheet to manage Block ID conflicts when adding mods to a Minecraft Server. Example spreadsheet is attached.

Description: Column A contains a list of available numbers (e.g. 1-20 and 31-50). Column B contains a list of numbers a new mod tries to use up by default (i.e. 1-25). A number in Column B that is not in Column A must be changed to the nearest number that is in Column A. In this example, numbers 21-25 in B are not available in A and must be changed to 31-35, respectively. Column C should be the result of changing Column B (i.e. 1-20 and 31-35).

Issue: The main problem is that as a given cell in Column B changes, that number needs to become unavailable in Column A for the next change. For example, when 21 in B returns 31, 31 can no longer be used for the next number in B (22).

I have been able to use VLOOKUP or INDEX/MATCH functions to lookup the numbers to be changed.

View 2 Replies View Related

Changing Text Into Numbers And A Hyphen

Jul 27, 2013

What is the formula to cover multiple different text that could come up? I could have an answer that is NA, Yes and also a blank cell and if I could I would like to have it set up as follows:

NA = -
Blank Cell = 0
Yes = 1

View 2 Replies View Related

Macro With Changing Column Numbers

Jun 14, 2009

Is it possible for excel to monitor Row J for a letter "Y". If a "Y" is put into a cell in row J a macro runs. If the "Y" was in column 6 I need the macro to copy A6 B6 C6 D6 E6 F6 etc to other cells on a different worksheet.If "Y" was in column 7 it will copy A7 B7 C7 etc to the same other cells on a different worksheet.

View 14 Replies View Related

How To Stop Random Numbers Changing

Mar 1, 2012

I generate a range of random numbers in column C. Then I perform another task and then copy the randoms numbers in the range c4:c(some row) into another sheet.

But the two columns are not the same? I want to copy the same random numbers into the other sheet.

Code:
'Generate random numbers
Lastrow1 = Range("c65536").End(xlUp).Row
ActiveCell.FormulaR1C1 = "=RAND()"
Range("c5:c" & Lastrow1).FormulaR1C1 = "=RAND()"

'Finding a specific row number and copying random numbers

[Code] ........

View 5 Replies View Related

Changing Numbers From Numerics To Words

Jun 20, 2006

Is there any formula or easy way to automate the changing of a numeric input into words, eg if you input 100 in one cell, the output in another would be "one hundred"?

View 9 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

Vlookup With Changing Column Index Numbers

Sep 25, 2009

I've 2 worksheets, Summary and Sales. I need to get the total sales figure from the "Sales" worksheet onto the Summary worksheet. Sales data are pasted onto the worksheet monthly. Tried using vlookup but problem is, the column containing the total sales figure may vary and I need to update the vlookup formula. Another problem is that sometimes the Total sales (the lookup value) is not in the first column. Tried using the Index and Match combo but cannot get it to work. I've attached a simple mock up to hopefully illustrate my case better. I believe the experts in this forum should find this rather easy!

View 14 Replies View Related

Convert Numbers To Thousands Without Physically Changing Each One?

Apr 18, 2014

To convert numbers (975,095.97) to thousands (975.1) without physcially changing each one?

View 2 Replies View Related

Stop Excel From Changing Numbers To Dates In CSV File?

Jun 6, 2005

When opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.

The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.

I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?

View 3 Replies View Related

Changing A Series Of Numbers In An Excel Sheet Column

Apr 29, 2009

I have a series of numbers in one column. Is there some way I can divide all the numbers together by, say 10, without doing it manually?

eg.

12
15
15
25
20

should become

1.2
1.5
1.5
2.5
2.0

Is there a way in Excel to do that?

View 3 Replies View Related

Excel 2007 :: Changing Huge Numbers Of Hyperlinks (formulas)

Feb 5, 2014

I've got a main workbook (excel 2007) with a main spreadsheet where i group all the information (text and values) about brands and models linked to 10 workbooks (brands) with several spreadsheets (one spreadsheet=one model).

On the main spreadsheet I've got 16 lines for each model which they have the follow formulas, wich are linked to other workbook / spreadsheet (Mar/Gato)

1)

IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE));"";VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE))

and will return text values


2)

IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;82;FALSE));"";VLOOKUP(V844;[Mar.xlsx]G!$A$3:$CE$78;82;FALSE))

And will return number values


3)

IF(M844<>"";HLOOKUP($V$3;[Mar.xlsx]Gato!$A$2:$CE$78;2;FALSE);"")

And will return number values. The return value (;2 goes from 2 to 16.

This process repeats to all the 10 workbooks e and 250 spreadsheets

4) Table Array of the model spreadsheet

With regard to formula 1) and 2)

The problem is that the table array (on the model spreadsheet) could have diferent dimensions (becouse the launch year of the model) like this:

A$3:$CE$78 (2009 - Launch year)
A$3:$BR$78 (2010 - Launch year)
A$3:$BE$78 (2011 - Launch year)
A$3:$AR$78 (2012 - Launch year)
A$3:$AE$78 (2013 - Launch year)

And the return column (value) for formula 1) is always ;3;

And the return column (value) for formula 2) are like this:

;82; (2009 - Launch year)
;69; (2010 - Launch year)
;56; (2011 - Launch year)
;43; (2012 - Launch year)
;30; (2013 - Launch year)
;17; (2014 - Launch year)

The difference between above values is 13, it corresponds 12 months and the total column (12+1)

For the formula 3) the table array its the same, the only difference is instead of starting A$3, starts in A$2

5) The update of the formulas 1) 2) 3) for 2014

I'm doing this manually... The formulas are updated like this,for 2013, I've got table array A$3:$AE$78 and the return value (;17, then for 2014 i will have a table array A$3:$AR$78 and the return value will be (;30, and so on

5) Change the network drive

For other reasons I've to change my files to another network drive, something like this:

Q:KPI2014Main File

6) Main Goal

I was thinking using Excel's regular Find and Replace feature to change:

- The network drive designation
- the table array area
- The return value

But I don't know if this process ( Find and Replace) is safe and clean. I'm doing this manually and I've got on the main worbook/sheet 4000 lines with 3 formulas its 12000 operations!I'm on the 350 line....!

The main goal is to update the all formulas (links) to "brand" workbooks and inside "model" spreadsheets automatically with a macro.

View 2 Replies View Related

How To Prevent Pivot Tables From Changing Source Data Numbers Into Dates In Field Columns

May 13, 2013

why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?

View 1 Replies View Related







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