How To Divide Cell If Cell Contains Less-than Symbol
May 31, 2013
Column (B) contains two different units, either (ug L-1) or (mg L-1). I have this formula, =IF(B2="(ug L-1)",A2/1000,A2), which copies the value in column A to column C, so for cells that are in (mg L-1) units, it's a direct copy, but for cells that are in (ug L-1) units, it divides the value in A by 1000. However, I can't remember what the formula should be if the cell contains a "<" symbol AND is also in (ug L-1) units.
e.g.
< 0.004(ug L-1)
How can I change that to <0.000004?
View 7 Replies
ADVERTISEMENT
Apr 9, 2009
I am trying to write a macro where I divide the value of one cell by that of a second cell, multiply it by 500 and then to paste the outcome of the formula to a third cell. This is what I wrote
View 5 Replies
View Related
Mar 8, 2007
Need a formula if there is one to extract from a cell only that data which is to the Right of the symbol > and to the left of the symbol (
View 3 Replies
View Related
Aug 24, 2007
I need to take cells such as the following from A1 down:
Baker, Helen
Brown, Joe
Smith, John
Roberts
Thompson, Karen
White, Daniel
Wright, Lisa
I need to move this to the next column but where there is a cell such as the 4th without a comma it needs to add ", N/A".
Basically it should make "Roberts" from A4 become "Roberts, N/A". I think it needs something to search for a comma and if it's not present to add that to the end.
View 3 Replies
View Related
Nov 7, 2009
Is there a way to define the end of a cell in a find/replace? I.e. I want to find characters like this:
;;[space character][end of cell]
and replace with
[end of cell]
(that is just delete the ;;[space character] bit when it is followed by end of cell)
View 5 Replies
View Related
Oct 19, 2011
I am trying to put a (C) within a cell but it keeps converting to the copyright symbol. How do I amend this so I can just have a C in brackets?
View 2 Replies
View Related
Jun 20, 2009
I simply cannot insert a one eighth symbol in my spreadsheet.. I used to be able to see this in the Insert Symbols grib but now all I get are lots of question market.. if I use
selection.value = ChrW(8569)
the symbol does appear until I select the cell and press return, after which point it turns into a question mark.. I'm sure its something to do with my settings but I can't figure out what.. this is really important for me.
View 9 Replies
View Related
Jun 1, 2007
having a problem with a vlookup when the cell value contains ~
ie. John~Smith
it returns an #NA even when my lookup table has John~Smith in it.
View 9 Replies
View Related
Dec 16, 2009
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).
These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.
View 9 Replies
View Related
Jun 11, 2009
I would like to embedd a symbol into a cell. I dont want it to be done by using Fonts. By symbol I mean Harvey Balls (Plan, Do, Check, Act) symbol embedded the same as the "and" (&) symbol. I have seen it done once on a spreadsheet but did not get a copy and I would like to recreate it as a way of visually showing the status of an action item.
View 14 Replies
View Related
Dec 28, 2012
I have a column ful with numbers and would like to add an h (hour) after every number. I found that it is possible for valuta (dollar sign). Is there a way for random symbols that are not in the valuta list?
View 3 Replies
View Related
Nov 29, 2011
I am trying to add a * at the end of a value in a cell, used to signify a note at the bottom of the page. However, as the cell contains a formula, excel thinks I am adding a multiplication sign instead.
Is there anyway to place a * at the end of the value, to show up in the cell as text? I would prefer not to have to paste the value of the cell and then add the *...
View 3 Replies
View Related
Dec 3, 2006
Cells in Columns A & B (57 rows deep) have a real time data feed and can have values of blank, "H", or "R". The H and R can remain in the cell for any length of time. I would like to have an audible ding go off whenever a new H or R appears in columns A or B. Is there a cell formula that can do this? I think a macro solution would not work, because the workbook has a macro that activates every 10 seconds, so I assume a macro solution would interfere if both macros happen to activate at the same time.
View 9 Replies
View Related
Jan 9, 2009
I need a code for filtering the cell's content.
What i want to filter and delete is all/any text found following after the symbol ">" .
View 9 Replies
View Related
Mar 10, 2014
I have a lot of cells containing numbers. Some numbers are small, some are big.
I want to divide all numbers which are greater than 300 by 1000.
IF cell A2 > 300 THEN divide by 1000.
How to do that? Is it possible to convert value in the same cell like using conditional formatting principle?
View 7 Replies
View Related
Jan 11, 2012
I am trying to do:
Say value in A2 and A3 is 4.5 and 5 respectively
and the value in A1 is and 1 respectively
B2 and B3 have zeros.
Case 1 :
Now I would like to have value A1 in C2:AZ2 till the value in A2 gets exhausted.
Hence C2 = 1, D2 = 1, E2 = 1, F2 = 1, G2 = 0.5, H2 = 0, I2 = 0 ... and so on
Case 2 :
Values in C3:AZ4 should be A1 till A3 gets exhausted. But the constraint is the values in the above range should start from/next cell when we left CASE1
Hence C3 = 0, D3 = 0, E3 = 0, F3 = 0, G3 = 0.5, H3 = 1, I3 = 1, J3 = 1, K3 = 1, L3 = 0.5 ...
I am using the below formula -
For CASE-1 : C2 is
=IF($A$2>0,IF(SUM($B$2:B2)$A$2,$A$1,0),0)
D2 is
=IF($A$2>0,IF(SUM($B$2:C2)$A$2,$A$1,0),0)
and so on...
But this works only if the numbers are whole numbers, I tried a lot but I could not get a formula which works with integers. The above formula will fail if the number is say 4.5 -having decimal part.
For CASE-2 : C3 is =IF($A$3>0,IF(COUNTIF(C$2,1)>0,0,IF(SUM($B$3:B3)$A$3,$A$1,0)),0)
D3 is
=IF($A$3>0,IF(COUNTIF(D$2,1)>0,0,IF(SUM($B$3:C3)$A$3,$A$1,0)),0)
When the numbers in A2 and A3 are decimals and not the whole numbers.
View 9 Replies
View Related
Mar 24, 2007
How do I enter a value into a cell and divide that number automatically by another value all within the same cell.
For example..
I want to enter 20 into a cell and have it change into 17.24 (20/1.16) automatically all within that same cell.
View 3 Replies
View Related
Jan 13, 2012
I'm trying to make a formula but are failing. This is the data:
Cells:
a1= ant-gel
a2= ant - gel
a3 = size 1 - 6
a4 = size 1-6
a5 = book - 5
a6 = book-5
a7 = good book
I want a formula that displays "True" if cell contains a "-" symbol AND the dash symbol is surround on both sides by a number (a space can go between dash and number)
I want the results to go in column B
For example, if a1 contains a dash and dashes are surrounded on both sides by a number, then "TRUE", otherwise "FALSE.
therefore:
b1 = FALSE
b2 = FALSE
b3 = TRUE
b4 = TRUE
b5 = FALSE
b6 = FALSE
b7 = FALSE
View 2 Replies
View Related
Apr 11, 2014
I have attached a sheet as an example, as i am trying to Divide a cell by the number of populated cells in that column e.g column (A) has a table of 16 but only has 13 populated cells in that column, the sum would be something like this =sum(A18/13. Sometimes the column will have less and at times more populated.
test sum skip blank cells.xlsx
View 2 Replies
View Related
Jul 9, 2008
I am wondering if it is possible to create a formula in a cell that will divide whatever number is entered into that cell by a set or fixed number.
ie. I enter the number 9 in the cell and when I press enter, the 9 is instantly divided by 15 and the answer is displayed in the cell where I just typed the 9.
View 14 Replies
View Related
Feb 27, 2009
Im wondering if this can be done in excel..
2 columns
if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30%
this is the way I was trying to do this...
=IF((O2:O6722)=0,DIVIDE(L2:L6722).3)
I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?
View 9 Replies
View Related
Oct 9, 2009
I have a sheet that when printed out, will have 1 or more check boxes (depending on a formula) beside various items. Basically, if in a datafield it shows that there should be 3 boxes beside a line, it will show similar to this (except the O's are check boxes).
Usable 3 times per day. O O O
In the actual symbols list, there are no such symbols. I think there must be another type of symbol available as I use: ►
I grabbed that symbol from another spreadsheet, and I have no idea where it came from. I have wanted a similar arrow that points to the left or up, but I have no idea where to find them. I only know that the spreadsheet accepts a copy/paste of this symbol, and it does not seem to be related to any particular font set.
where I could find a "empty box" symbol, and also tell me where symbols like ► come from.
View 9 Replies
View Related
Aug 19, 2009
I need to use the following symbol (,, ▬ ) to show when my prices went up or down but when I try to use them in the VBA, they are replaced by a ?
I considered using the CHAR function, but its 255 characters do not incorporate the one I need.
View 7 Replies
View Related
May 22, 2008
I have a large list of data.
Approx 10,000 rows.
All I'm looking to do is delete in each row everything after the / symbol.
So everything after a forward slash gets deleted.
All my data is in ColA from A3 downwards.
Ideally would love a macro I can assign a button to.
As an example;
info@123croieres.com/Categories/travel-and-tourism-lodging.htm
So the above after the macro run would look like;
info@123croieres.com
View 9 Replies
View Related
Feb 16, 2009
I'm logging temperatures and I want to add the degree symbol "" to the end of the numbers, how do I do that? It's easy if I just use one number (#), but how do I do it when adding the lowest and highest in the same cell, e.g. 33-35?
View 9 Replies
View Related
Feb 20, 2007
i would like to automate the inserting of the symbol ...
i can do in a cell as the first entry, but i can't do at the end of a string's entry in a cell...?
ActiveCell.FormulaR1C1 = ""
or
ActiveCell.FormulaR1C1 = "Schukas"
View 9 Replies
View Related
Jul 10, 2008
I am trying to create a document and i'm not sure if this is possible or not, but i'd like to type in a number in column A (1-10) and then in column B have a symbol appear corresponding to the number.
So if i type in 5 in Column A, Column B will have 5 squares pop up (or something along those lines), etc.
View 14 Replies
View Related
Oct 19, 2008
I would like to count all fields which contain the symbol checkmark.
In another instance I am using the formula
=COUNTIF(B124:B137,"X") to count all fields
which contain an X.
However I don't know the reference for the symbol checkmark.
View 4 Replies
View Related
May 23, 2013
I am trying to create a custom number format that will put a plus sign in from of the value in a cell if the value is positive.
Here is what I have been using:
+#,###;-#,###;"On Forecast"
There have been a few problems with this. Firstly, the values are have two decimals, and I have not been able to adjust it accordingly. Secondly, there are some cells that contain only a zero, and the custom format above changes the zeros to 'On Forecast. Other than these two issues, that custom format works.
View 1 Replies
View Related
Nov 4, 2013
I am wondering if there is a symbol for "text" that I can use in a formula. I.e., =if(C="text", "", INDEX(C:C,MATCH(D16,A18:A23,0)))
View 4 Replies
View Related
