Displaying Months From Cells As Their Text (not Numerical)
Oct 14, 2009
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 ?
I have a variable COMTXT that loads (via loop) several short lines of text to form a "mouthful" of comments together. But have 2 issues with it.
1- I need to add a carriage return after loading each short line of text. [code] Module1.COMTXT = Module1.COMTXT + Sheets(3). Cells(Module1.COMCODE, 1) [?CODE]I have try'd [& vbCrLf &] in many syntax's to no avail.
2- When displaying COMTXT to a sheet, its too big for the cell ! and not able to enlarge the cell without major disortion to rest of sheet. How do I acheive this and allow the text to display like a textbox or label, covering many cells and rows.
The basic spreadsheet consists of a list of names down the left hand side with 19 columns across - one for each art activity (I have pasted a copy of it below - but it doesn't paste so good!). Each person has selected their three activities and this has been recorded by placing a 1, 2 or 3 in the relative columns. Each person has been told that they will get a place on two out of three of their chosen activities (in which case we will change one of their choices to a 0).
What I would now like to be able to do is to create a mail merge in Word which looks at each person and then goes along that row to find out which columns have a 1, 2, or 3 in it. The heading for that column would then be used as a merge field so that I end up with a word document which looks something like this:
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?
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.
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 was looking to have a Word for example 'Apple' but want it as a number e.g '10'. I want to use it so that I can add up the prices of items by just clicking on the name of the item. So If I want to add up 3 items e.g. an apple , a banana and a pear which all cost £10 each I want to be able to click all three items and get the total of £30 displayed in a certain box.
I am using the following math formula in cell C12:
=IF(B12="","",IF(B12<2,-1,INT(B12/5)))
What I want to do is if cell B3 equals a text value of the word "All" then cell B12 would display nothing. If B3 equals any other value then the math formula above be used.
I'm using an Excel spreadsheet as a database (mostly text based), so that in columns 1 and 2 are names and in column 3 is notes. I'm keeping all the columns single height (ie: not wrapping text). Where there is nothing in column 4 onwards, the text from column 3 displays across the subsequent blank fields.
I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells.
By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank.
I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0 If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0 If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0 If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
I'm having to re-learn formula commands in order to continue to use my spreadsheets (I had tried to get the Quattro Pro converter to work in Excel 2003 with no success). Actually, I would have stayed with QP since I find it much more user friendly, but with Excel being the standard spreadsheet that most people use, I figured I better get used to it. Anyway, I'm currently trying to find out how I might get the same text that shows up in varying cells in an otherwise blank column (other cells contain formulas, but no text) to also show up in another fixed cell (which can be where I put this formula).
I am trying to display watermark text in a cell based on the value of another cell. For example:
If in Column B, cell B2 states "Mileage", Column C, cell C2 needs to say "Please enter Start and Destination...". I want column C (cell C2) to be my input cell as well hence the reason i'm avoiding the use of an IF formula.
I'm open to a VBA solution if this works? Tried conditional formatting however it will only display colours, not text?
Excel 2007. At the bottom of a column of data I'm trying to reference the cell $AG4. There are 340 total columns. Each additional column to the right needs to reference the next cell in column AG. So I'm trying to drag and copy horizontally from $AG4 to $AG340. When I try just the single column it just copies $AG4 into everything to the right. When I have to columns ($AG4 and $AG5) and grab both to try to copy horizontally the numbers increasing it just does $AG4, $AG5, $AG4, $AG5, etc.
Is anyone aware of some way to use the " dynamic named range approach" to only select the cells with numerical values in a column and name this range?
I've looked at the examples on this site but can't find any solution to this particular problem although I have a feeling that this should be possible.
