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
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.
Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.
View 5 Replies View Related[URL] .... If you take a look at the picture, what I'm trying to do is add the list of Prices up so that I can differentiate the amount received in $ and in £.
I need to add up all the numbers that don't have a $ in front into the 1 cell, and all the ones with the $ in front into another cell.
FEdLkuj.jpg
Is there any script that I generate 6 numbers of these numbers from the list? so they do not recur after another example 7,12,13,19,26,33
View 2 Replies View RelatedIs 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 RelatedI 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.
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.
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 View Relatedwhen 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 RelatedHow 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.
I need to allow only in textbox1 Numbers & decimal Point.
View 4 Replies View RelatedIs 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".
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: .....
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 ).
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.
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 RelatedI have to create lists of data in the following format:
123121321,12321215,121351213 (numbers with , with no spaces)
These numbers are sent to me in excel in columns and I need to convert the columns into rows using the format above.
The formula I have been given:
=INDIRECT(ADDRESS(((ROW()-1)*10)+2,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+3,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+4,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+5,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+6,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+7,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+8,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+9,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+10,1,1,TRUE,"SSOs")) & "," &
INDIRECT(ADDRESS(((ROW()-1)*10)+11,1,1,TRUE,"SSOs"))
currently I only get 10 numbers per row
I need at times for up to 500 numbers to be listed on a row. However even when I try to extend the formula it still only gives me 10 numbers per row and breaks up the 500 numbers to 10 per row.
Is there another way to combine up to 500 numbers in one row?
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;
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
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 RelatedCode writes decimal numbers next to each other instead of adding. I have the following code on a very simple form:
View 5 Replies View RelatedHow 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 ##.##
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.
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
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 RelatedI 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 RelatedI'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.
How to convert decimal numbers which represent minutes into time format? Like this
Sheet3 GH1MinutesTime212.9729729700:13:00325.9459459500:26:004116.756756801:57:00
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): ....