I extracted data going back to 1980 from Bloomberg and realized that BB uses abbreviations rather than pure numerical values. For example, 3,000,000 is "3M," 300,000,000 is "300M," etc. I need a macro that converts "M" into the actual value, "B" into the actual numerical value, etc. For example, a macro that converts "3M" into 3,000,000. I know that I can identify the values in VBA with "###M."
View 7 Replies
I have a large database from a central appraisal district. In the database the city is stored as a numerical value. The problem is I need to convert the numerical value for the city to a text string with the actual city name. For example the values to the attached example database are as follow:
excel help forum 2.xlsx
024 = Hurst
026 = Fort Worth
025 = Euless
017 = Mansfield
013 = Keller
The issue is, I need to convert this column of numbers into the string of the actual city name. Is there some type of command, or macro that I could use to automate this process? Attached is a small example copy of the database.
So I am starting to get ambitious with Excel now and am looking at creating a macro that will automatically create a pdf file of several worksheets at the same time. However this is my problem. Using the current version of Excel there is a wonderful record macro button, however when I try to include a function that would create a PDF, it says that I need to save any changes before creating the PDF. However when I save the file, I have to stop recording which defeats the whole purpose of creating the macro in the first place.
How can I use the record macro function to create my pdf macro without learning visual basic? If I cannot, how to write what it is that I am trying to accomplish?
creating a Macro to automatically group rows that has no numerical value, for example:
1. 13.4
2. 0
3. 0
4. 33.3
5. 0
and so on...
So for this exercise I need excel to automatically group row 2, 3 5 with one click, and leave other rows with actual numbers visible on the sheet.
a colleague recently used my pc and "messed it up". when i enter a value in any cells, it is divided by 100, eg, when i enter 1, it shows as 0.01.
View 2 Replies View RelatedI'm trying to see how accurate people's work predictions are to actual work completed. So I have these formulas:
=IF(C15=0,"",(SUMIF(L$24:IO$24,"Est.",L15:IO15))) - total estimated days
=IF(C15=0,"",(IFERROR((SUMIF(L$13:IO$13,"Act.",L15:IO15)),""))) - total actual days
But the problem of course is that people estimate a load of work and only fill in the actual days as they go along, so the accuracy of comparing one to another is almost always misleading.
What I want to do is only count the values in the weeks Estimated if the Actual figure is also there (L24:IO24), which is always the cell directly on its right.
I have a spreadsheet that is used for sales territories. In column A, I have customer names, in column B, I have the State abbrev., In column C I would like to return as salesperson's name based on the State.
Aco IL John Smith
ABco OH Jane Doe
Cco WI John Smith
Czco IL John Smith
Dco WY Adam Scott
Fco UT Adam Scott
FDco CT Bill Jones
etc WI John Smith
So I need a formula to enter in column C, that will return the correct salesperson based on the state in column B. (when auto filled down)
For example: IL,WI,MN,IA,ND,SD = John Smith
OH,PA,KY,MI, = Jane Doe
etc
creating functions in excel and would like to hire someone who can assist me in creating the following functions: Column A (For Numerical Values Less Than or = to 100): Column B Value should be (+10)
Example 1: If a numerical value of 90 is placed in Column A then Column B Numerical Auto Value should be 80+10 (190). Example 2: If a numerical value of 35 is placed in column A then Column B Numerical Auto Value should be 35+10 (45) and so on. Another important point is that if the values in column A are copy and pasted Column B should automatically discharge the values visibly as the examples stated above is this possible? If not i'll need to have this done.
Also there are other numerical values that must be added in Column A (basically for every increase in numerical value of 100 there are to be different numerical plus factors in accordance to Column A). Column A (For Numerical Values greater than 100 but less than 200): Column B Value should be (+15).......
I want to set up an if statement that does the following, where 'a' and 'b' are numerical values.
If 'a' is not a multiple of 'b' Then
...
Else
...
End If
How can I write this so VBA can understand it?
I've got a series of values in a column as follows:
10 Mb
10 Mb
1000 Mb
1000 Mb
114 Mb
128 Mb
128 Mb
Obviously 1000 mb is more than 10 mb, but it sorts as second in the list
because it starts with a "1". How do I tell Excel to sort by the entire
numerical value instead of the first number?
it seems that excel is deciding what type of array this is by evaluating the type of the first cell in each column and applying this type to the rest of the array's column. Problem is that i want all values to be brought in, text and numerical.
Dim VArray As Variant
Sub DA201()
If FileThere(Path & "DA201.xls") Then
GetDataTowers Path & "DA201.XLS", "", "DataRange", Sheets _("Towers").Range("CStart"), False, False
Else
MsgBox ("File DA201.xls not found. Click OK to continue")
End If
End Sub
Public Sub GetDataTowers(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim n As Integer
Dim row, m As Integer
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else....................................
I would like to be able to take an address like 18387 S 113Th W Ave and return it as 18387 South 113th West Avenue. I am a slightly familiar with lookup formulas, but I have not had any luck making this work.
View 9 Replies View RelatedSo I am trying to make a macro that will update monthly forecast data to what the actual production/consumption data was. The production/consumption numbers come in separate workbooks (i.e. "Jan14", "Feb14") and need to be linked to the 'Book' main file. The path needs to the Jan14 needs to come into the 'Book' file, not just the values. I would like to be able to just have a button above each month that will just automatically load that month's data when it becomes available.
View 1 Replies View RelatedI have a range, A1:A10 that I want to sum, min, and max. I want to ignore any text or #DIV/0! values in this range. I know how write the formulas except how to ignore the text and cell errors. Can someone steer me in the right direction?
View 5 Replies View RelatedI need to convert levels to numerical values and then: Firstly, add together two vlookup values THEN divide by 2 to get an average AND THEN see if this average AND a second, individual lookup value are above a specified another value, which may be different. IF all these criteria are set, return, "yes" if either the first or second, or both criteria are not met then "no"
Or put it another way. if lookup values A+B/2>"5" AND C>"3" then "yes", Else "no"
Lookup chart:
P11
P22
P33
P44
P55
P66
1c7
1b9
1a11...............
I'm making a spreadsheet for the homes I'm looking at purchasing and wanted a way to calculate automatically which one has the most things we're looking for.
So for example, if a home has a walk in closet, it would say "yes". If it doesn't it obviously would say "no".
Is there a way to assign a number value in a totals column where "yes"=1 and "no"=2?
Or a way to make colors equal a certain value?
Where I'd make all the "yes" items green and then a green cell = 1, a yellow cell = 2, and a red cell = 3.
I am trying to conditionally sum numbers from a matrix. On the vertical axis, there will be duplicate values (text) who's rows should be summed, and on the horizontal axis I need to sum in between two numbers that will be in sequential order (such as dates). The real tough part for me is that the data field that I will be summing from has non numerical values, like dashes. Obviously, I don't want to sum the dashes, but it throws off any formulas I have tried (like sum arrays).
My hopes are to use as little memory as possible so my file size wont get too big and to not have to use macros (I do not really use them), although I am willing to try if they are basic.
In the example below, I want to sum rows for the letter "A" and in between numbers "2" and "4."
# 1 2 3 4 5
A2 6 5 4 2
B4 5 6 4 1
C-----
A4 2 1 2 1
E1 1 2 2 2
Y Variable: A
X Variable Min: 2
X Variable Max: 4
Correct Result = 20.00 = (6+5+4+2+1+2)
I often have to OCR files in Adobe and then copy the data as a table into Excel. Sometimes when I move the data over these little boxes with question marks inside will appear infront of the data in the cells. I can delete the square from the spell by just backspacing after it but I don't want to have to do that for hundreds of cells individually. Googling around I found a formula: =RIGHT(A1, LEN(A1)-1)
That formula will get rid of the box, once the box is removed I need to be able SUM the values, but I am still unable to. I've tried to copy and paste the values before using the SUM function but that does not work, I've also tried to SUM before removing the box but that does not work either. I've found that if I double click in the cells after using the formula that it will allow me to use the SUM function but that would require me to double click hundreds of cells individually. Also, just in case this matters, a little green flag appears in the upper left corner of the affected cells after I use the previously mentioned formula.
Is it possible once you have assigned text a numerical value (example: Adam = 12) to add them together? (example: adam =12 and bob = 8, therefore adam + bob = 20)
I currently have data in the form of: "510 E. 96th Street, Suite 400 Indianapolis, IN 46240"
And need a formula to extract only the state abbreviation from the cell, any ideas? I currently have the formula, =MID(O333,(SEARCH(",",O333)+2),2) but it only works if there is one comma for example, it works perfectly on this data:
2K MEDIcal 105 West Torrance Boulevard Suite 106 Redondo Beach, CA 90277
writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.
e.g Company = Co. or
Insurance = Ins
Please see my attached excel document for an example.
I have over 27000 lines so doing this manually will take forever!
Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.
Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.
The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.
I am attempting to count from a spreadsheet the reference number of a customer (numbers and text) based on two criteria.
1, If column G= Requested
2, Column I = Meeting
Count Row E
I thought a sumproduct was best and have started using it for the first time, I thought this should work but I keep getting a #NUM! error.
I have tried with numbers and it works but the non numeric aspect is difficult.
[Code].....
I am trying to do is a summary of numerical values, based on several conditions.
column a column b column c
local loop live cross connect
loop A cancel 100
long haul live 10000
long haul pend canc 1000
loop Z live 100
x-conn live cross connect
In column a, i have several choices and i want to focus on those choices that fall into column B "live" or "pend canc" and then only have numerical data in column C
I've tried several different formulas like
=SUMPRODUCT((a30:a38="Loop A")*(a30:a38="Long Haul")+(b30:b38="Live"),cX30:cX38)
I know the result of this should be 111000 but it's not working...I usually get n/a. I know about using cntl-shift-enter to use the formula properly.
I can get one variable to sum up properly using =SUMIF(M9:M21,"Live",BW9:BW21)
but, that leaves out anything that is pend-canc
I have also tried =SUM(IF((a9:a21="long haul")*(b9:b21="live"),cX9:cX21)) which gives back a value that is accurate.....
I have the following 'numerical values'
2510000.011
3010000.011
3510000.011.3
2518000.032.5
3018000.043
3518000.043.7
2530000.125.5
3030000.145.8
3530000.176.2
With the first two values as criterias, I need to find the 3rd and 4th value
Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3
Hi all, first post here.
I've been asked to do something in excel i can't seem to get my head around.
My client provides me with data in the form
1c, 1b, 1a, 2c, 2b, 2a, 3c etc... where 1a is the lowest.
I need to find a way of working out the difference between these, e.g.
edit: 2a - 1c = 5
I've been working on it and have made some headway by splitting these with MID function and then using CODE to try and work out a unique ID for each, but i can't help feeling there's an easier way!
I am working up a workbook template that will be used to process survey results. The way I ultimatley want it to work is that the user can paste data from another application into a worksheet in Excel, and then view analysed results on a second sheet (which are processed via formulae on a third, hidden sheet).
The problem I have is this: the data from the program that's capturing them are text values. For example, column B contains answers to a question where participants rate an experience, and the values are either "Excellent", "Good", "Fair", "Poor", "Awful". I want these to be converted to numerical values, on a scale where "Excellent" = 5 and "Awful" = 1, so that I can then average these.
So, the question is: Can I somehow tell Excel that "Excellent" = 5 and "Good" = 4 etc, and then use AVERAGE(B:B) on the text data and get a number back?
I did think of having an intermediate sheet that used VLOOKUP to create a copy of the first sheet (where the user pastes the text data) with the text replaced by numbers. But, because I don't know in advance how many rows will contain data (i.e. how many survey results there will be), I have to assume on the high side and copy down 50,000 rows. But, this takes ages to calculate.
how to find a certain criteria and delete the entire row that the criteria is in. I am more interested in finding certain criteria with vba, in my case any text/non numerical values and clear contents from that cell for the entire worksheet while retaining the rest of the cells that have numerical values in them. My data has --- in cells that represents missing data which would be easier if it was completely blank.
View 2 Replies View RelatedI'm putting together an Environment Threat Assessment for work and want to automate as much of it as I can to alleviate the amount of time ppl have to spend putting values in. My main problem is I have three very important columns: Likelihood, Success Rating and Risk Rating. Likelihood and Success Rating values will be selected from the following:
VL - Very Low
L - Low
M - Medium
H - High
VH - Very High
Each of these is given a value: VL = 1, L = 2, M = 3, H = 4, VH = 5.
The Risk Rating is the result of multiplying the Likelihood and Success Rating. So a Likelihood of Medium and a Success Rating of Low will give a Risk Rating of 6. Therefore, the cells on my spreadsheet would look like this:
Likelihood Success Rating Risk Rating
M L 6
The issue is I want a couple of things to occur. Firstly I want keep the values in my Likelihood and Success Rating cells as letters (ie. VL, H, VH etc) rather than numbers, while still generating a number result in the Risk Rating cells. Secondly I'd like to be able to change the colour of the Risk Rating cell to reflect the number it gets assigned. For example Green for < 8, Amber for 9 – 14 and Red for 15 – 25. I've been trying to do this with array formulas and am basically going around in circles with no success.
I have a formula that counts all instances of the letters a, b and c in a range and assigns them a value of 1 unless they are in the M column in which case a, b or c counts 4.
=SUM(COUNTIF(J2:AB2,{"a*","b","c"}),OR(LEFT(M2)={"a","b","c"})*4)
This worked well but now I need to modify the weightings across this range. My new goal is to count all instances of a,b and c in the range J2:AB2 but have M column a,b, c's = 2 and N:R column a,b,c's equalling 4. Letters other than a,b or c count as 0.
Example:
J2...K2...L2...M2...N2...O2...P2...Q2...R2...
A....B.....B.....C.....B.....D....B.....C.....E
the output of the above sequence should be
1+1+1+2+4+4+0+4+4+0 = 21
My company is soon to be rolling out a new payment system, and I'd like to be able to track commission for everything I sell. Briefly, we get paid via a percentage of the company's gross profit, and the way in which the company gets paid is tiered. (the cellphone business)
So, If a customer activates a plan thats below $39.99, we get paid X amount. If it's below $59.99 we get paid Y amount, and so on and so forth, there are multiple different tiers.
What I want to do is set up a list via data validation so that I can pick what plan they have (via the name of the plan) and have it return a numerical value (ie $39.99) and also have the ability to pick text messaging, etc as options so that the workbook will add the monthly rates (39.99 + 14.95) and then have it return value for the right priceplan tier.
If this is confusing, I apologize for not being more clear, but attached is an example. The top one is what I'm actually trying to code, but the bottom is completely filled in, so you can get a more clear sense of what I'm trying to do. Honestly I'm not even sure if it can be done.
