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


Excel 2003 :: Concatenate Text Strings From Column Using Multiple Criteria Condition (formula)

Dec 20, 2012

I am looking for a way of creating the following conditioned concatenation.

I have two tables, let's call them "summary" and "detailed".

The "detailed" table is something like the following:





The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:

ID (unique)
VOL (concatenated)

V01, V03, V05

V01, V04


PS: I have people using this table with office 2003, so compatibility is necessary...

View 1 Replies View Related

Excel 2003 :: Formula To Combine Delimited Numbers

Dec 11, 2012

I am using Excel 2003 and trying to create a formula that will combine comma delimited numbers from two or three cells in the same row and output them to another cell in the same row as comma delimited numbers, sorted with duplicates removed.

So the data is something like this ...

Cell A1 ... 1,5,8,19

Cell B1 ... 4,26,1,8,8501

Cell C1 ... 1,6,1301,12

I would like to consolidate these into cell D1, sorted and distinct ...

Cell D1 ... 1,4,5,6,8,12,19,26,1301,8501

View 1 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:


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

Fixied Decimals With CONCATENATE

Jun 11, 2009

I'm converting metric to British values and have no problem with fixing the decimals when displaying the number. However, I'm wanting to CONCATENATE the unit of measure to the conversion and end up with 12 places beyond the decimal.
Entered formula is: =CONCATENATE(CONVERT(T5,"cm","in")," in")
Results in 159.055118110236 in

But = CONVERT(T5,"cm","in")
Results in 159.1 which is what I want.

View 2 Replies View Related

Concatenate Dollar Amount, Keep Decimals

May 21, 2008

When concatenating a text and dollar amount that is the sum of two cells, adding up to $42.90, I only get "42.9". is there a function (perhaps similar to ROUND) that will make sure concatenated numbers always have 2 decimal places?

View 4 Replies View Related

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)


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


This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows ( through 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

Excel 2003 :: Why Numbers Change When Save In CSV

Jan 31, 2012

I enter 13 digit number in a cell, when I save my Worksheet in CSV format the number is changed.

I enter 9328627000014 (this is a barcode) After saving this file the number is displayed in the Formula Bar as 9328630000000 and in the Cell it show up as 9.33E+12 When I go to Format Cells, this cell is selected as Scientific. When I change it to Number, after saving it again it returns back to scientific with the old display. Also when I enter 2 digit number in another cell in another column and I choose to be displayed as number with 2 decimal places, after saving the file it returns back as General format. When I am saving this file as CSV a window pops up with a warning that my file may contain features that are not compatible with CSV. I can not choose text format in the cell because it is a datafeed and it has to be read correctly as number.

I use Microsoft Office 2003.

View 1 Replies View Related

Concatenate Formula (drag Down The Attached That Will Place Zeros Infront Of Any Numbers That Don't Have 9 Digits)

Jun 17, 2009

I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.

View 2 Replies View Related

Excel 2003 :: Sort To Include Only Work Orders With All Numbers?

Aug 27, 2013

Using Microsoft Access to pull from a database work orders. Some of those are what we call "dummy" work orders and have a letter in them, real work orders are signified by a 10 digit number. I would like to sort and eliminate the letters to find appropriate data. I am assuming I will need to copy into excel and do some sort of formatting but not sure where to start.

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

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 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?

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

Excel 2003 :: Formula To Sum Every 6th Row Of E Column?

Feb 4, 2014

The following formula was, several weeks ago, very graciously offered to me from one of Excel Forum's contributors.


My request was to find a formula that would add each 6th row starting in row e8 (e8+e14+e20+e26+e32 etc. through e6782) in column "e" when the column was 6782 rows deep from top to bottom. (i am not trying to add every number in column e, just each 6th row, starting at e8 and going through row e6782).

I entered the formula into my spread sheet and, voila, I had a sum that I assumed was accurate for my spread sheet of ticket sales. I began to question the functionality of the formula when I altered the E8:E6782 parameters (which represented the gross ticket sales) to E4:E6778, in an effort to sum up the E4 values e4,e10,e16, e22,e28,etc. . . (which represents the net values after commissions were deducted). The difference in the two sums (e8 values Versus the e4 values) was incorrect and did not represent the appropriate commissions (which should have been 15%).

View 1 Replies View Related

Excel 2003 :: Sum Formula With Conditions?

Apr 28, 2013

I would like to find out, how would look formula in excel 2003 to perform process like discribe below:

(in cell K1 formula)
=sum (A1+B1) then if sum of A1 and B1 is lower then 100 then add C1 but if sum of A1 and B1 is higher then 100 then subtract 100 and add C1.

View 9 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 :: Calculate Formula For Third Value Of Week?

Nov 9, 2012

On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:


(this is not the actual formula but simplified, that's not really important).

It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):

I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula

(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be:

Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective

I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.

View 9 Replies View Related

Excel 2003 :: Formula To Look At Name And Find Previous Name Above It

Mar 26, 2012

Excel 2003. I have a list of names in column A, dates in column B, I need the difference between dates for the people in column A, I am doing it now with the formula shown but I need a formula to put in column C that will look at the name in column A, for that row, and then find that name above it and give me the difference between the to dates in column B. So in the example below if I put the formula in C7 it would look at A6-A2 for harry and then give me the difference between B7 and B4, 6 days, if there is not a match return blank.

Excel 2003ABCD1NamesDateDays Between2tom2/8/123****3/2/124harry3/9/125tom3/10/1231=B5-B26tom3/12/122=B6-B57harry3/15/126=B7-B48****3/26/1224=B8-B3Sheet1

View 4 Replies View Related

Excel 2003 :: Copying A Formula And Skipping Every 5th Row?

Aug 31, 2013

I need to copy a column of data from Sheet1 to Sheet2, but skipping every 5th row in the original data on Sheet1, but without leaving any gaps in the resultant column in sheet2.

At the moment I've been manually changing the formula but there must be an easier way.

eg. Copying data from Sheet1, A1 to A11, I would have in Sheet2, starting at A1 :

Any formula I can enter that would be dragable and still skip every 5th row ?

Windows XP, Excel 2003

View 5 Replies View Related

Converting CountIFS Formula To Excel 97-2003

Jan 22, 2014

I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.


in the above formula AM relates to a list of morning shifts.

The problem I have now encountered is that the system at work is still using excel 97 - 2003 which does not recognise Countifs.

way to adapt the formula to be compatible with Excel 97.

View 7 Replies View Related

Excel 2003 :: Formula To Determine If Someone Is 21 Years Old Or Not

Mar 10, 2014

I am trying to come up with a formula to determine if someone is 21 years old or not. After I enter their DOB if they are 21 or over I would like the cell to turn Green, if not I would like it to turn red. I have tried alot of different formulas that primary pertain to days and months but nothing for years.

View 3 Replies View Related

Excel 2003 :: Referring To Cell Value In COUNTIF Formula

May 7, 2013

I am using Excel 2003

Cell B13 contains the row number of the first row of data on the sheet and B14 contains the row number of the end row of data. This is because the number of rows vary over time.

I'm trying to do a COUNTIF to test for duplicates in column R (contains integers) and I want to specify the range to count by referring to the value in B13 and B14 in the formula and so I have the following which returns FALSE for some reason even if the value in R19 appears more than once in the range ...


View 3 Replies View Related

Excel 2003 :: Formula To Pull Data From One Sheet To Another?

Jan 16, 2014

I have Excel 2003

My spreadsheet has multiple lines of data for multiple items (have a specific item #) and I need to be able to enter an item # on a different sheet so that the requested information can be pulled from the data source. The items have information on multiple rows. I need to have all of the rows pulled into my output file when I specify the specific item. #.

I have attached a spreadsheet showing the type of data is contained and what my desired output would look like.

View 1 Replies View Related

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