Combining Simple Formula With Text Concatenation Produces Too Many Decimal Points
Feb 18, 2014
This is my formula with concatenation: =K3/K1*100 & "c" & " Each"
This is the result of the values in K3 divided by K1: 6.41666666666667c Each
I would like to limit the decimal places to two: 6.41c Each.
I have tried to format the cell and none of the formatting options have any effect.
If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.
View 9 Replies
ADVERTISEMENT
May 22, 2014
RCA Page for forum.xlsx
I'm trying to get entries from one table to appear in another. The first table is like this:
DateDate
Customer Column1
Description Column1
Customer Column2
Customer Description2
Created By
The table I want to put the data into looks like this:
Date
Customer
Description
Created By
View 2 Replies
View Related
Sep 29, 2009
I've tried to look for a solution on the forum, but nothing seems to come up. I've attached a file to help show what I'm trying to resolve.
Column A of the file shows an amount, when summed, give a total of -3.5725E-09.
Each of the figures in column A only has 2 decimal points and if I manually total up the numbers on a calculator it give me zero.
Does anyone know how I can get rid of the -3.5725E-09 without another formula? I need the balance to be zero.
View 10 Replies
View Related
Jul 3, 2013
I have a few cells that contain decimals that do not appear as decimals. If the number is 50.1, it only shows up as 50 in the cell. I've tried formatting it to a number with 2 decimal places and to scientific and when I do that the number displays as ##. When I do general formatting it only appears as 50.
I used a formula to pull the number into a different cell, however, and in that cell it shows up as 50.1.
Is there anything I can do to make it show up as 50.1 in the original cell?
View 2 Replies
View Related
Nov 18, 2008
I am in need of a macro which would be capable of doing the following;
Replacing a sequence of number's & decimals such as 58.6073.1 with 58607310000
Essentially, the code will just need to remove the two decimal points & to suffix those 4 zeros onto the end of the number sequence.
View 3 Replies
View Related
Mar 5, 2009
I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty.
I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points. Also dont know how to integrate it into existing formulas and macros
View 5 Replies
View Related
Jul 17, 2007
Is there some kind of function that will remove decimal points from a number? What I'm looking for is:
1231.5 = 12315
123.15 = 12315
12.315 = 12315
1.2315 = 12315
View 6 Replies
View Related
Feb 10, 2014
I've read various "solutions" to my request but it's Greek to me. Simply stated I occasionally have to create worksheets that involve dollars and cents. My question is how can I get Excel to stop dropping the zero if its the last digit to the right of the decimal point? $42.30 becomes $42.3, etc.
I've looked through the preferences/options and can find nothing to rectify this. Any way to do this so that keeping the zero becomes the default for any tables, lists, etc that I create in the future?
View 4 Replies
View Related
Oct 24, 2012
I have tried, through control panel - region settings etc, to change the default number of decimals to 0. I often use the Number format using the shortcut CTRL + SHIFT + 1 but then have to reduce the 2 decimals down.
I have recorded a macro to apply this kind of formatting but the disadvantage of this is that I cannot undo any actions before that point - which has gotten me into some trouble on a few occasions .
Is there a macro that I can use that adjusts the "Normal" cell style number format from General to Number (with thousand separator but 0 decimals) each time I open a new book? As merging cell styles is also quite a mundane task..
View 5 Replies
View Related
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
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
Sep 13, 2012
I have a data example
Cell A1 which contain text I.E = "Words"
I have link B1 To cell A1 I.E = A1
Now what I want is I want the last two words or any alphabet between alphabet
to be bold
W O R D
Example
WORD or WORD
Any alphabet between word to be bold upon my requirement.
Instead of linking that cell to another cell , i want any value when entered into that cell.
That alphabet automatically becomes bold as this will not be requiring linking to that cell
View 4 Replies
View Related
Mar 26, 2009
I am using this formula:
=IF(O6>0,"",(VLOOKUP(A6,Material!A2:G43,7,FALSE)))/2000*O6
and it produces #N/A in the cell that contains this formula. If cell A6 has an item selected in it I made from data validation-list then the output is $0.00.
How can eliminate anything from showing if the list is blank in cell A6?
View 7 Replies
View Related
May 21, 2008
I'm outputting some cells to a CSV. At first I tried using the SaveAs function but that renamed my worksheet and didn't work for ranges (as far as I know)
Sub Export()
'Set myADPFile = "C:ADPPCPWADPDATAPRSHWEPI.CSV"
Dim myADPFile As String
myADPFile = "C:PRSHWEPI.CSV"
Dim x As Byte
x = 5
Open myADPFile For Output As 1
While (Not (Sheet3.Range("A" & x).Value = NA))
Print #1, Trim(Sheet3.Range("B" & x).Value), ",", _
Trim(Sheet3.Range("C" & x).Value), ",", _
Sheet3.Range("D" & x).Value, ",", _ ..........................
The values are correct. However, the excess amount of spaces is not. Using the Write function just encases everything in quotation marks. Replacing spaces isn't a good solution because the first row MUST be in that format, and removing all spaces would require more code to put spaces back in. Is there a way to get rid of all the excess spaces surrounding each value?
View 2 Replies
View Related
Nov 6, 2009
I was wondering it were possible to reference an error. I'd like a cell to have white text if $O19 produces a #N/A error. I tried formula
=($O19=#N/A)
and the formula
=($O19="#N/A")
View 2 Replies
View Related
Jan 19, 2010
After doing some searching I found a macro here which imports multiple text files into one workbook, keeping each text file as a seperate worksheet, each worksheet named the same as the original file name (minus the .txt extension).
However, it adds a blank line between each line of text. So I get this:
Pinging 172.27.133.29 with 32 bytes of data:
Reply from 172.27.133.29: bytes=32 time=232ms TTL=62
Reply from 172.27.133.29: bytes=32 time=62ms TTL=62
Reply from 172.27.133.29: bytes=32 time=65ms TTL=62
instead of this:........
Pinging 172.27.133.29 with 32 bytes of data:
Reply from 172.27.133.29: bytes=32 time=232ms TTL=62
Reply from 172.27.133.29: bytes=32 time=62ms TTL=62
Reply from 172.27.133.29: bytes=32 time=65ms TTL=62
This doesn't happen when I use the import wizard, or when I just open the file from the File -> Open menu.
My text files are always the same format (number of lines, etc.), so it's easy to delimit and insert forumlae as needed, but those extra lines are breaking a macro I've previously written to parse the data.
View 9 Replies
View Related
Feb 15, 2013
Excel 2010
I have the following SUMIFS formula that produces the desired results until I get the the last qualifier, displayed in red:
=SUMIFS(JBHAMOUNT,JBHMONTH,C18,BNSFCATEGORY,B20,BNSFLOCATION,"Alliance")
The result of this formula is: #VALUE!
View 2 Replies
View Related
Apr 15, 2012
I have to make a excel document in which I have length and width in feet and inch format.
E.g. 10.01 in which 10 is feet and .01 is inch
I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .
Just like the calculator here does.. [URL] ........
Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...
Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????
Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000
View 7 Replies
View Related
Aug 18, 2009
I have a spreadsheet with many many columns...the top of each column has a string (it's a bird name). Below in the same column are various blank spaces or letters (defining species abundance, if there), example:....
View 2 Replies
View Related
Jul 17, 2007
I want to concatenate the text of several cells into one cell that satisfies some condition (simular to a SUMIF). For example:
1 AB
2 CD
3 DE
The formula I'm looking for should return ABDE in case of 1 and CD in case of 2.
View 6 Replies
View Related
Apr 21, 2012
I have a formula in the target workbook in cell C3 =-'data.xls!'np2011'
However, when UI copy the formula to D3 , the formula remains as =-'data.xls!'np2011'
2011 after np is the year. The names in the source workbook have been named np2011, np2010, np2009 etc
np2011 is a range name in the source workbook. When I copy the formula to d3, it should change to -'data.xls!'np2010'
In the target workbook I have the years in cells C1 to L1. How can I get the formula to change when copying/ Alternatively can one concatenate the range name to C1 for eg such as ="-'data.xls!'np&c1&"' I have tried to do this, but cannot get it to work.
View 5 Replies
View Related
Feb 19, 2012
I have the following in VBA:
Code:
ActiveCell.FormulaR1C1 = _
"=mod_a1&"" ""&mod_b1&"" ""&mod_c1&"" ""&mod_d1&"" ""&mod_e1&"" ""&mod_f1&"" ""&mod_f1&"" ""&mod_g1&"" ""&mod_h1&"" ""&TEXT(mod_i1,""m/dd/yyyy"")&"" ""&mod_j1&"" ""&TEXT(mod_k1,""h:mm:ss"")"
I only want the user to see the string, not the concatenation formula. So how do I get VBA to turn this formula into an actual string.
I have seen some functions using ByVal and Evaluate with this sort of thing but this is just different enough that I can't figure it out.
View 4 Replies
View Related
Jul 4, 2013
How would I modify this concatenation formula:
Code:
=concatenate(A2&" "&B2)
I would like it not to put a space in if A2 is blank.
View 2 Replies
View Related
Jul 9, 2014
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
View 3 Replies
View Related
Jun 26, 2008
I am looking for some examples of simple math (Adding, Subtracting, Dividing & Multiplying) using Text box values in a userform.
I have 6 textboxes. 3 of them for data entry and 3 of them for calculating from the other three.
I am not looking to work of any worksheet, it must be done in the user form.
View 9 Replies
View Related
Jul 29, 2009
I need to extract text from a cell between 2 predetermined characters. Below find all possible cases and what result (after extraction) should look like:
data cell Extracted
o9.5-105 9.5
o9-105 9
o10.5-105 10.5
o10-105 10
o9.5+105 9.5
o9+105 9
o10.5+105 10.5
o10+105 10
u9.5-105 9.5
u9-105 9
u10.5-105 10.5
u10-105 10
u9.5+105 9.5
u9+105 9
u10.5+105 10.5
u10+105 10
Basically I need to extract numbers between (to the left) o or u and to the right + or -. The string ends always with 3 numbers if it. I have also attached excel file of these two rows for your convenience.
View 3 Replies
View Related
Jun 18, 2008
Is there a way in Excel/VBA to calculate the width of a text string (in points or inches) with a specific font/font size/font attributes? Right now I have to use a static character count which assumes worst- case character widths for the font (Arial 8 point) but leaves unused whitespace to the right when the text string uses narrow characters.
View 6 Replies
View Related
Mar 5, 2014
I'm wondering if it's possible to write a formala that if a certain condition is met, performs a formula referenced in another cell. For example: Column A contains a list of formulas. In column B is the condition, let's use Work Type as an example. The result (or the formula I need) would be in column C. So if the work type is "Type1", then the formula in the corresponding column C cell would perform the formula in cell A5. If the work type is "Type8", then the formula in the corresponding column C cell would perform the formula in cell A2.
Is this possible to do without VBA?
View 6 Replies
View Related
Mar 26, 2009
Using Excel 2003. I have a very basic SUM formula I am using to show how many forms are still in-house. Pretty simple.
Column D is how many came in.
Column F is how many were completed.
Column E is what is left.
To get the daily count I take the previous day's Column E, add to today's Column D and subtract today's Column F.
=(E2+D3-F3)
Puts the results in E3.
This formula is copied on down the worksheet which results in:
=(E3+D4-F4), with the results in E4. And so on down Column E.
The problem is that if there is no data entered in Column D or F, the results from E are carried (copied?) as far down as the forumla. i.e., if today's column E results in 4, then that is displayed for as far down as I have copied the formula. What I'd like to do is set the formula so that if there is no data in columns E or F, then there is nothing displayed in Column E.
View 3 Replies
View Related
Mar 10, 2013
Trying to work out array formula.
basically if B2 = list of figures in range(F1:f22) then "G14" otherwise "".
{=if(b2=(F1:F22),"G14","")}
doesn't work.
View 5 Replies
View Related
