I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!
Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?
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
I have a formula in column A of the following sheet. The problem is that when the entry in cell H contains a letter as well as a number then the formula in column a returns 0.
Also if the value for false in the forumula is changed from 0 i.e to 1 or 2, then a value is returned in column A (this is shown in the red highlighted cells).
Basically what I am trying to do is build a formula but I have hit a wall it seems like.
what I want to do to complete the formula is, I have 2 names in mind that I want to be able to enter one cell and have excel translate them into a number. So for example if I enter the name John in that cell excel will know that John means the number 3 and then tomorrow I erase John from that cell and enter Jim and excel will know that Jim means the number 2.
But again I want to be able to enter a certain body of txt into one cell and have excel recognize it as a number. How can this be done?
I have a monster of a sheet going and am working for research purposes. I dislike the interface of SPSS so I use excel to boil down my variables to a smaller state, to which it then goes back to SPSS for the hardcore analysis. Unfortunately, this study has values of "N/A", "Missing", and "." (not entered), so it makes it a bit tricky for me. Say I want to check 10 different scales to see if any are true: =IF(OR(FJ6>=1,ZD6>=1,....etc),"Yes","No"))
Problem is the blasted program is reading ".", "Missing", and "N/A" as greater than one!!!!! I've found a way around by making monster formulas that first check that the field is not equal to those three text values, but there must be a faster way! To see what I mean, put a period in one cell (like A1) and write =IF(A1>=1,"Yes","No") and you'll see it returns a Yes.
I am a teacher and need a column of student names (which will all be different) but also need to assign them a numerical value of 1 to analyze my data. How can I do this?
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 need a formula that looks at a Cell and if it contains any text at all it will return a numerical value into a seperate cell. I have done this before but for some annoying reason i cant remember how i did it.
The formula contained within these cells equals =MONTH(C84) [the date 2 cells above it]... to which I've formatted the cell to "mmmm" and it displays "January" across the board. Can anyone explain why this is occuring and how I can fix it please? I'm aiming to graph this information and can't accurately do it using the moving monthly system i've put in place if I can't change the months dynamically like this.
Secondly, and on a side note. Line 84 currently takes todays date (not currently todays date in the example screenshot) (=TODAY()), and to get to the previous 13 months I deduct 31 days. I can foresee this as being a problem further down the track especially when leap years come into play. Is there a function in Excel where I can simply deduct 1 month from another? =MONTH(C84)-1 ?
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 need to create a formula that searches column E for text, let's say "BANANA", and when it finds that text, the formula returns the numerical value of the cell in that same row in column B. Let me know how this can be done.
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.
I am creating for the pupils at my school. Comparing the grades they were predicted and have achieved I would like to give them a score.
I have already achieved this but it means wasting 3 columns for each subject to work it out. The ambition is to do all the working out and assign a point score value in 1 column.
The spreadsheet it attached if anyone would be kind enough to have a look and see if there may be a formula which will do what I need.
I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").
I am quite inexperienced with Excel and I am struggling to understand how should I set up a formula, within the data validation function to achieve the following:
I need to restrict a certain set of cells, for people to be able to input only the following values: "1", "0.5", "A", "H", "0.5A" (or 0.5a), "0.5H" (or 0.5h)
And I would need the data validation tool not to include blank cells.
I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need Art no. Product Name Brand Group Category Subcategory y-123 abc lux hair care shampoo hair shine shampoo A-123 bca loreal skin care anti acne anto acne soap
Main file Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales y-123 abc lux 120 110 105 A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory y-123 abc lux hair care shampoo hair shine shampoo
I have two (2) different values in the same column one value is text (INV) the other is a time date stamp 05/18/2012 10:48:32. The text i want to return in a seperate column for these two is if it is INV then the result is "PENDING" if it is a date 05/18/2012 etc. then the result would be "PAID" example:
Payment Status 05/08/2012 10:30:12 INV 05/17/2012 08:27:37 INV and so on...................
(the |'s represent cell borders, if it isn't obvious)
What I want is a formula that can grab the text, then produce a total score. So, for my example, the result would be 7, totalling the scores for item1, item2, and item4.
I know that the score lookup is trivial (just a VLOOKUP), but I can't figure out how to do the scan for the data from a single cell. I want the items to remain in a single cell if possible.
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:
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.
I am looking to return text based on the maximum value in a table.
Dog 2
Cat 10
Horse 4
Fish 27
Lion 1
Let's call the column with the animals over it "A" and the one with the numbers "B", and the rows are 1-5 to make explanations easier
I am looking to have a formula return the name of the animal with the highest three numbers (fish, cat, horse), but don't need the numbers. I am pretty confident this would be an index match formula, but I'm not too familiar with those functions.
This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.
I have 3 columns of numerical data. In my fourth column, I would like to return the minimum value from the 3 previous columns (=min). However, instead of returning the lowest number, I would like it to return the column title (=text) of the lowest number. I haven't been able to locate a function that does this.
I am compiling data from several different, variable, worksheets. I am using concatenate to add the name of each worksheet into a formula to pull the appropriate data.
However, it is returning the text of the formula instead of the results of the formula. How can I make it calculate the result instead of simply displaying the formula text?
Store 1 Store 2 Store 3 Store 4 Top Selling Second Third Fourth
[code]....
I am trying to find two formulas:
Formula 1: Formula 1 will be entered into rows 6:9 (in this example). I need it to return the highest selling product (by store) in order form highest selling to lowest selling. I have a makeshift formula already but it cannot report in order (only the highest selling) and it cannot get past the one return index( limitation. When it finds two high sellers it prioritizes the ranking based on how well that product has sold across the other stores. Product 1 sold 13 times across all stores. Product 2 sold 9 times across all stores. So if product 1 & 2 tie for Store 3 (as shown in the example) Product 1 would be chosen based on its sales across the row.
Formula 2 : Same as formula 1 but with Stores (in column 6-9). It reports the highest selling store and treats ties the same way as formula 1.
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes.
A B C E F G M S Get value from G M S 5G M T M T 6M M U M U 7H M V M V 8K
