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