Use Letters Corresponding To Value In Formulas
Apr 16, 2008
I'd like a function that converts a letter to a numerical value and returns a mean score for those values over a range. The values for each letter are:
A*100
A90
B70
C50
D40
E30
F20
G10
U0
So in the range G4:AB4 there could be 3 letters (G4:I4)
A* F E - their numerical equivalents in the table above = 100 + 20 + 30 = 150. The mean score for this range and expected output = 50 (150/3)
I have enclosed a sample spreadsheet. The range of grades that needs calculating is G4:AB4. Expected output should be in AC4.
View 3 Replies
ADVERTISEMENT
May 27, 2006
I have a matrix with default probabilities. I have this matrix in my worksheet. The user will give the number of periods to be calculated with and it will result in a new matrix with default periods over time of choice.
Private Sub UserForm_Click()
Dim numberof_text_Change As Single
Dim initial_text_Change As Single
Range("U36:AC44").Select
Selection.FormulaArray = "=matrixpower(R[-10]C:R[-2]C[8],numberof_text.value)"
End Sub
matrixpower is a function that works perfectly well. It takes one matrix as an argument and the number of periods to use. It then simply computes the chosen number of powers of my matrix. I have a userform that has an inputbox called numberof_text which specifies the number of periods that the user wishes to use in calculating the chance of default. Hence I would like to use this variable as an argument in the formulaarray. This doesn't seem to work! I only get "#VALUE!" in every cell. If I replace "numberof_text.value" in the matrixpower function with a number it works fine. What to do?
What the function needs to do is take an argument which is the matrix I have on my spreadsheet. Take the number of times to take powers of the matrix and print it on a pre-specified place on the worksheet. If I cannot use variables with FormulaArrays, then is there another good way of choosing the matrix to use with "powermatrix"?
View 2 Replies
View Related
Oct 30, 2007
I'm trying to update a series of different sheets in a book based on what month I have filled in data for. For example, at September Month End, I will fill in data vertically. Now, I have different tabs that reference certain cells in that column. There row number never changes, but once a month I have to go through and increase their column letters by 1, and there are a lot of places to update. Can anyone lend me some ideas or a solution to how I should go about using formula/macro to update these cells.
View 9 Replies
View Related
Jul 29, 2014
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
View 13 Replies
View Related
Dec 7, 2009
I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
View 5 Replies
View Related
Jul 24, 2012
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
View 3 Replies
View Related
Mar 9, 2009
how can I seperate small and big letters from one collumn, example:
NAME
name
NAME
NAME
name
name
View 11 Replies
View Related
Nov 17, 2009
Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?
I have to letter a list whihc means setting up a loop using character codes.
I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"
of course the easiest would be to pick up a column value as a letter
NT values do not get a number
A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####
i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.
View 11 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Dec 15, 2008
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies
View Related
Dec 11, 2013
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
View 11 Replies
View Related
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 Replies
View Related
Jan 16, 2014
Is it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies
View Related
Aug 24, 2009
I have a spreadhseet with various functions on it and what I am trying to do is this.
Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.
What I am trying to do is get cell F4 to return certain arguments.
E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"
I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")
Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")
If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"
Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
View 5 Replies
View Related
Dec 12, 2007
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
View 11 Replies
View Related
Dec 11, 2008
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies
View Related
Feb 26, 2014
Tallying entires from a PDF into excel. Such that
c=1000
d=15000
e=50000
f=100000
g=250000
h=500000
i=1000000
j=5000000
k=2500000
l=50000000
In a cell I would like to type =c+d+c for example and have it say 17,000 rather then me having to type in =1000+15000+1000.
View 4 Replies
View Related
Jun 24, 2009
I would like to create a column with letters from alphabet in a sequence. If I write A and in cell below I put B then highlight the two cells and drag down I get a repetition of A and B. How do I get the following alphabet letters ie. C,D, E etc.?
View 7 Replies
View Related
Jan 16, 2014
I created this data worksheet with many tabs and multiple users. On the main tab, something weird has happened and I have no clue how it happened or how to fix it....
View 5 Replies
View Related
Sep 30, 2008
I am basically looking for a method to fill in information through VBA in excel into a 2-D grid. Ideally I would like this to work but it does not.
View 14 Replies
View Related
Feb 25, 2007
I need to average the following row of letters. Is it possible?
A B D C C C C A B D A A A A A = AVERAGE OF THIS ROW (IE B)
View 14 Replies
View Related
Nov 21, 2011
I have a column of domains.
I need to add "http://" to the beginning of these domains. How can I do this?
EX. I have column A with about 27 cells of domains "website.com". I want to add the "http://" to the front of them.
View 1 Replies
View Related
Feb 28, 2007
Using =column() in cell A1 returns "1" as column A is column 1 with b being 2 etc.
How can I get it to return "A".
View 9 Replies
View Related
Aug 3, 2007
I have a validation drop down box type thing... when I select the drop down, the lower-most option is highlighted as a default. How do I make it so that the upper-most option is highlighted first?
View 9 Replies
View Related
May 16, 2008
I have a few columns of letters (ranging from A to Z) - onyl one letter per cell. I need to be able to deduct them such that they result in a numeric difference (ie D-B=2, D-A=3). Is there any way this can be done in VBA? (The actual columns are in a word table, so would be great if there was a VBA way to do this, not an excel function).
View 6 Replies
View Related
Aug 11, 2008
How would i extract the first 3 letters of the first 2 words in a cell?
the cell may have 1 to many words in it.
I would like to exclude words like of and the...
i.e. the univeristy of washington should be uniwas
View 9 Replies
View Related
Apr 17, 2009
This formula will add up the letters D,N,H with one value
Like =SUM(COUNTIF(B4:AZ4,{"D","N","H"}))*12 will =36
How can I add bN =17 bmN =19 bD=24
=SUM(COUNTIF(B4:AZ4,{"bN","bmN","bD"})) to add up to 60
View 9 Replies
View Related
Dec 15, 2006
IS there a formula to add the letters PR to the end of each number in a column.
View 3 Replies
View Related
Feb 22, 2007
Im looking for a way to check what a workbook name starts with
What is the code if i want to do that. Is there is examples. What should i google after
I want to check if the first 4 letters in the string is equel to example "JVIT"
View 7 Replies
View Related
