I am trying to write some code that will repair errors in a standard workbook. One of the errors is a blank cell that needs to be changed to "325F T 350F". How do I insert these symbols/operators into the VBA code?
View 6 RepliesLotus 123 had a function where you could graphically, in a cell, show plus symbols(+) or negative symbols (-) for the value of the data. For example if you had a column of numbers that you calculated from the previous year that indicate the change, Lotus, in the next cell would allow you to graphically display the symbols that indicate the strength of the change. So if your result was a positive ten then ten plus symbols would be shown. If it was -20 then 20 negative symbols would be shown. Now the numbers I am looking at could be 600,00 and I believe there was a way to to proportion the symbols for example for every 100,000 put in one symbol so the result would be 6 (+) signs.
View 5 Replies View RelatedHow can i formulate the following in a formula:
Sales of product A (product A belongs to product class ABC) divided by the total sales of all products in product class ABC
What would be the correct mathematical terminology for the following:
I have a pool of 100 people and I am tracking their hours worked.
I want to present the % of people that fall in to the 3 largest categories
E.G.:
00:00 - 25:00 = 5
25:01 - 50:00 = 10
50:01 - 75:00 = 15
75:01 - 100:00 = 25
100:01 - 125:00 = 15
125:01 - 150:00 = 10
150:01 - 175:00 = 10
175:01 - 200:00 = 20
In this case the largest groups are:
50:01 - 75:00 = 15
75:01 - 100:00 = 25
100:01 - 125:00 = 15
55% fall in to this bracket.
What we are targeting as a business is that the majority of worker fall in to a similar range each month. So the challenge for my team is to increase the % value month on month. the close to 100% the better they are doing.
Equally what in Excel could I use to automate this calculation.
I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator.
In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!
'Initializing tagrow so we can test what train we are trending'
tagrow = Cells(12, 2).Value
'Only Propylene flows need adjustment in A and B trains, calculate everything'
If tagrow = 7 Or tagrow = 8 Then
Sheet2.Calculate
Range("D15:D115").Value = Range("C15:C115").Value / 1000
End If
I'm trying to meet set criteria in b5 using AND the first works if both conditions are met in two cells but bur won't on one cell?
Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("c5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub
But this dosen't
Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("b5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub
I have tried searching for a good example of how to use logical operators on Mr Excel however the search is frustrated by "Small Words" in the search field ie IF, AND, NOT ,OR are too short to search.
Can anyone list the formulas needed in Excel
+
_
x
divide
If UCase(TextBox1.Value & "," & TextBox6.Value) < UCase(currsheetname)
This works most of the time, but for something like:
Smith,Bob < Smith,Jim it will give erratic results. currsheetname is not declared so is a variant I guess.
I have an IF Function that I want to use to check mathematical errors between two sheets. For example Sheet2 is where I put all my material prices and Sheet1 cell A1 shows the total amount that is one Sheet2. On Sheet1, I have an IF Function in B1 that does the same calculation A1 does, but will display "ERROR" if A1 does not Match B1.
The IF Function works, my problem is that I want this to work from B1:B100 on Sheet1. But on Sheet2 the next SUM would be from row 42 to row 59.
How can I make and IF Function from B1:B100 on Sheet1 offset every 20 rows on Sheet2
For example:
Sheet1 B1 would be:
Formula:
[Code].....
Sheet1 B2 would be:
Formula:
[Code] ......
I need to do this without having to manually enter each range to sum up.
Is it possible to set a range using comparison operators? ie. if a number lies within x and y then such and such happens.
View 6 Replies View RelatedI am trying to alter the type of operator used in one formula based on the result of an argument in a separate cell generally as follows: If Cell 1 equals something then use the ">" operator, otherwise use the "=" operator. I appreciate this sounds really simple and I'm having a major brain fade as I cannot figure out how to relate the two arguments as follows: [If(Value 1 "Operator" Value2, then do something, otherwise do something else)] where the operator is pre-defined in another formula.
View 4 Replies View RelatedWe have just been upgraded to Excel 2010 with it's hideous menus. tell me where to find =, +,-,* and / signs in the menus before I go mad!
View 6 Replies View RelatedI'm trying to figure out how to reorganize data based on boolean values in two different columns. My goal is to write something in VBA that will look to see if the boolean in the first cloumn is true then procede to check if the boolean in the next column, same row is true. If that is the case, it will take several values from the row and move them to another worksheet. If the boolean in the first column is true, but the second column is not, it will look down the rows of the second column until it finds true then move several values from rows it has passed through. Here is an example of the sample data I will be dealing with.
Date Time Duration Boolean1 Boolean2
6/22/200715:0032TRUETRUE
6/22/200718:0058TRUEFALSE
6/22/200719:0060FALSEFALSE
6/22/200720:0060FALSEFALSE
6/22/200721:0060FALSEFALSE
6/22/200722:0060FALSEFALSE
6/22/200723:001 FALSETRUE
Based on the example data my goal is to move the data in a new worksheet that would look something like this
Date Start End Duration
6/22/2007 15:28 16:00 32
6/22/2007 18:02 23:01 299
I have Three different combinations which are in cell A & B, These combinations are with similar signs as in A1=2- & B1 = 2- (& A1 = 2+, B1=2+). Then I have numbers with no signs which A1 =2 B1= 2. My approach is to limit to these and these combinations only and use a formula which is uniformally applied on these combination. I want A1 and B1 to take only like signs like -- or ++ or without any sign. The rest shd be ignored. IS there a way of sorting two columns which have different combinations liek -- or ++ or -+ into a area where u can apply a particular formula? Hence first i take a number and move signs on the left and then apply this
IF A7 = 2- . B7 = 3- THEN
IF(OR(RIGHT(TRIM(B7),1)="-",RIGHT(TRIM(B7),1)=" ",RIGHT(TRIM(B7),1)="+"),LEFT(TRIM(B7),LEN(TRIM(B7))-1)*3,B7*3)
In cell C2 I want to compute the value of 5x^6 + SQRT of 6, where the value of x is located in cell A%. What formula would I need to enter into cell C2 to do this?
View 9 Replies View RelatedI use an excel spreadsheet alot for billing in the construction industry. There are premade formulas in this spreadsheet. When no input value is present, the total column(s) either show a "$0.00" or show "#DIV/0!". How do I hide these results for the rows that do not yet have any input? I have seen it done on other speadsheets
View 3 Replies View RelatedI need to display the currency symbols for all European & Asia Pacific countries. The only ones available that I could find in excel help are , , . Does anyone know if there are more available? This seems limiting.
View 9 Replies View RelatedI have lists of phone numbers with parenthases and hyphens. like (555) 333-4444
I would like to remove all symbols and leave just the numbers.
I would alsolike to add the number 1 in the first position.
so (555)333-4444 would end up 15553334444
is there a way to use the "1/4" symbol as 0.25 in a calculation?
View 9 Replies View RelatedI've got a workbook with multiple formulas e.g.:
t=S/D
TCh=Ch*(S^2/2D)*(D/Q)
TC=Co*(D/Q)+Ch*((i-D)/2i)*Q
which are built by implementing the symbols:
t, S, D, TCh, Ch, Q, TC, Co, i
I'd like to substitute the symbols in following sequence:
t = a
S = b
D = c
TCh = d
Ch = e
Q = f
TC = g
Co = h
i = x
Questions:
1) Which formula could substitute the symbols automaticly?
2) If there are up to 100 formulas in the workbook in the range of A1:E100,-
would it be possible to to set the substitution for the mentioned range?
See the attachment.
I have a column with e mail addresses that all have > < this symbols in the beginning and at the end, how can I remove them from the column?
example
<Ana.Gomez@taca.com>
Im looking for a formula to count different symbols in a row. I am using =COUNTIF(A1:A1600,CHAR(251)) to count 1 symbol and =COUNTIF(A1:A1600,CHAR(252)) to count another. what i need is a formula to count all symbols no matter what they are. As in this case they will only be the 251 + 252 a formula just for that would be great.
View 4 Replies View RelatedI have a worksheet with what appears to be symbols or text boxes; squares, some with a check mark in them, some without. cannot select any of them to edit or delete them, I can type in the cell behind the symbol, the symbol hides the text. I can copy and paste them, but not delete them. how to identify them to edit and/or delete them. Password protection and all cells are unlocked as well. I have stumped as well as other forums.
View 4 Replies View RelatedCan i find a list anywhere of the new Excel 2007 ALT numbers to give the symbols eg Alt0186 = the degrees symbol in 03 but doesnt work in 07?
View 10 Replies View RelatedI need to delete cells that contain certain characters (symbols) and space. Say I have some columns/rows like -
Column 1 Column 2 Column 3 Column 4 Column 5 Text Box Text Box Power Power Place Nice Nice.Plus Jump Jump Over High (Jump) Car Black Car Green.Car Car's Color Cars Blue Color Green Red (Color) Black White
And now I need to have cell without those contain symbols or space or character like -
Column 1 Column 2 Column 3 Column 4 Column 5 Text Box
Power
Nice
Jump
[Code]....
VBA code for inserting "X" for wrong and Tick mark symbol for completed items.
View 5 Replies View RelatedI am importing content control information in a word file into excel by using a macro in excel. The information is coming across nicely but when the information about a checkbox comes across, in excel, that's what you see, a checkbox (☐ or ☒). Is there any way (formula or vba) to replace all the checkboxes with a yes or a no?
I wrote a formula that works:
Code:
=IF(G8="☒","YeS","nO")
But this will add this information to a new column and I would like to replace the information in the existing column (the form has about 200 checkboxes) and I don't want to add a bunch of new columns.
I also toyed with VBA:
Code:
Range("G1:G100").Replace "&", "No"
But when I try to paste the ☐ in the vba code, I get a "?" so I am assuming it doesn't recognize the ☐ as a valid character.
This is my qns
cell A1 having <txt>NEWM</txt>
cell A2 having <txt>RSQ</txt>
I need to display NEWM in B1 and RSQ in B2....
way to convert all symbols ( ) to their equvilant letters (u a) I know that I can do this manualuy with Find replace but I was hoping there was some magic that I am missing.
Please note that the exapples are only 2 of the symbols I need to convert the sheet could contain all of the varieties of symbol.
I have a spreadsheet that creates a named file using data entered into a field. For a hypothetical example, people can put their favorite car in A2 and, when the macro is run, it will SAVE AS the spreadsheet and name it with the value of A2. Problem is, some folks don't follow instructions and do things like HONDA / INSIGHT, or else something using symbols that are not valid for file names.
I know there are lots of ways to use Data Validation to prevent certain entries, but is there any way to prevent certain symbols? If not, can anyone help me with an Event Handler that would evaluate the data for certain symbols once the data is entered?