# 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
ADVERTISEMENT
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
May 26, 2006

I made a thread similar to this earlier but was unable to get to a desired end result.

I am trying to make a formula that will count the number of cells that do NOT have a fill color AND have a value over 1000 AND are greater than 60 days old as of the last day of the previous month.

Attached is a file with sample data so you can better understand

I included 2 formulas in the file that I am already using. Just in case that information is needed/relevant.

View 4 Replies
View Related
Apr 5, 2009

Is there any way witch i can use to make the auto-filter range bigger than 1000 cells?

View 2 Replies
View Related
Mar 12, 2014

I need a formula that returns the Nth number from a single column that contains a small number of values within a span of 1000 cells. E.g., within 1000 cells there are 36 with numbers and the remaining cells are blank. I need to obtain the first occurrence of a number in the column, then the second, third... to the thirty-sixth. The end result would be a column with 36 numbers with no blanks in between without loosing the correct order of the numbers.

I have excel 2003 and have been tooling around with the index and lookup functions with no success.

View 3 Replies
View Related
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
Feb 27, 2014

Is it possible through a macro that the cells where numbers is in percentage format should not show any decimal points (is should not roundoff) just decimal points should not visible. I am aware that this can be done by "decrease decimal" but data will be across thousand of rows manually it will be very time consuming.

I have attached a sample worksheet : Percent.xlsx

View 3 Replies
View Related
Jan 17, 2013

formatting time as follows :

I want to apply a score to the time cells ( decimals ) Basically i want to apply this set of criteria to a Row >

IF LESS THAN 0.00694 (10mins) then score 3

IF LESS THAN 0.01388 (20mins ) then score 1

IF BETWEEN 0.01388 & 0.9811 (20mins and 23:40:00) then score 0

IF GREATER THAN 0.98611 (23:40:00 ) then score 1

IF GREATER THAN 0.09930 (23:50:00) then score 3

I have this code :

IF(D9<0.0069444,"3",IF(D9<0.01389,"1","0"))*AND(IF(D9<0.986111,"1",IF(D9>0.993,"3",)))

it seems close to what i need but gives a #VALUE error ....

View 1 Replies
View Related
May 21, 2014

I'm pasting XML data into a spreadsheet and trying to create a vba tool so I can work with the values in a different configuration.

Here's an example of the XML data when pasted into Excel and how I'd like to organize it:

Capture.JPG

View 1 Replies
View Related
Jan 8, 2014

I have a column that looks like the following and I need to add the numbers:

27 skids

31 skids

56 skids

13 skids

The unit "skids" is constant. The answer I am looking for is "127" or "127 skids"

View 3 Replies
View Related
Jun 10, 2009

A1 would be the cell that users would enter quantities of 500, 1000, 1500, 2000 etc and based upon what they entered-cell a2 would give a corresponding entry of 10,20, 30 etc.

View 4 Replies
View Related
Jun 22, 2006

how can i do the following equation?

40$ if the sum is less than 1000$

75$ if the sum is less than 1001$ tO 3000$

100$ if the sum is greater than 3001$

C16 X C19 if sum is greater than 12000$

all should go in one cell

View 3 Replies
View Related
Apr 1, 2014

How to stop my 100% from changing to 1000% after it pastes?

Here's the pc of code that is performing the copy/paste from one sheet to another sheet within the same workbook..

Code:

With Sheets("BOM Worksheet").Range("C" & nr)

.Value = c.Offset(, -16).Value

.Font.FontStyle = "Bold"

.Font.Color = RGB(186, 85, 211)

End With

View 6 Replies
View Related
May 20, 2008

Based on the 1st example below, I have a file with 1000's of columns where every 3 columns has the same type of info....

View 9 Replies
View Related
Mar 4, 2012

I have about 300 cells filled with different formula's like the ones written below.

=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$C$1:$C$1000=D21),('Book 1'!$W$1:$W$1000))

=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$D$1:$D$1000=I21),('Book 1'!$S$1:$S$1000))

Spreadsheet works great, but I've realized that I should have expanded beyond the 1st 1000 cells (example: $1000). One book 1, my data entry will extend beyond row 1000.

Is there a way I can replace all $1000's on the spreadsheet with say $3000 without having to correct each of the 300 cells separately?

View 5 Replies
View Related
Sep 7, 2009

i have a figure of 1000 in cell a1. i want to divide it by a figure in cell b1 and have an answer in c1, or divide it by a figure in c1 and have an answer in b1.

in summary, cell a1 has i figure in it while cells b1 and c1 are empty until i enter a number into one or the other.

View 9 Replies
View Related
Oct 17, 2009

I am having a problem with vlookup. I have the formula working but would like to divide it by 1000 then multipy by 24. When I enter /1000*24 after the colon in the vlookup formula my answer is 0(zero). If I don't try to divide and multiply the answer is correct.

View 9 Replies
View Related
Oct 30, 2008

about making my code a bit more useful.

I started with a basic code as below which basically looks at upto 1000 part ids in column A and goes to my database to find corresponding Product Codes from a table in the database call PART

View 14 Replies
View Related