Give Letter Numerical Value And Use It To Format Another Cell?
Jul 22, 2014
I am a teacher and I want a column to turn a particular colour if a pupil has met their target grade and another if they have not. Our grades work as letters A* being the best then descending as follows A, B, C, D, E, F, G, U.
I have a column with the pupil's target grade (one of the above letters) and another column with their current grade. How do I get excel to understand the ranking of the letters and format the current grade column according to whether they have met their target or not?
View 8 Replies
ADVERTISEMENT
Dec 3, 2009
I want the A4 cell contains the calculation of B4 (but the number gained from the funtion row and if the B1 cell contains the number 10 the K(B1)=K10
[A4]=B(row())*K(B1)
View 4 Replies
View Related
Nov 3, 2009
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.
View 12 Replies
View Related
Apr 2, 2014
I'm working on a spreadsheet and I want the letter "E" to have a numerical value of zero, and not necessarily in just place as my sheet will be regularly updated. If you're a fan of golf, you will understand what I am doing. I'm basically making a custom leader board for a fantasy golf game that I'm doing, and I'd like for the letter E to represent the value of zero when I am adding up the scores.
For example
A1: -1
A2: -3
A3: 4
A4: E
A5: -2
A6: 3
A7: 3
A8: -5
Total: -11 (have a formula set up to only count the best 4 scores).
I want to be able to add up those columns, and since only the lowest 4 scores count, I need E to equal zero. I get that I could just put 0 there, so I'm being a little picky (in golf E represents Even par, essentially a value of zero for the purposes of this game). There are 20+ teams in the game so I need E to be recognized as zero in each of those fields.
View 14 Replies
View Related
Jul 27, 2007
Does anyone have any clue of a VBA function that gets a cell and Changes the type of itīs contense?
I have been using the Cstr function as in the next example but I dont get the result I want
hojaRES.Cells(i, 6) = CStr(hojaRES.Cells(i, 6))
If IsNumeric(hojaRES.Cells(i, 6)) Then
MsgBox "nothin changes :("
Exit For
End If
hojaRES.Cells(i, 6) = "0" & "34" & hojaRES.Cells(i, 6)
My problem is that i want to add 034 prefix to a telephone number, and as it treats the data as numeric; number 0 (before34) is deprecated
View 3 Replies
View Related
Nov 27, 2007
When using the formula '=COLUMN()' in cell A1, it returns the number of the column - in this case, '1' (for column A). Is it possible to affect this formula so that it returns the column letter (in this case, 'A')?
View 9 Replies
View Related
Dec 17, 2007
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
View 4 Replies
View Related
Feb 26, 2013
What would cause a pivot table to turn numerical data into a letter?
I have a field, cost center, that has about 15 different possible values in it that look like this: 141350, 141360, 141370, 141380, etc. When I compile the data into a pivot table and try to sort on pivot table everything looks normal, except that 141350 is returned as 'R'. If I change the value to anything else, it is fine, but as 141350 it's changes from numerical formating to a single letter.
I'm sure something was accidentally set to do that automatically.
View 2 Replies
View Related
Oct 15, 2009
Well following on from my question earlier, I am still playing with my timesheet/rota I'm developing.
I have a start time and an end time, which is formatted as hh:mm then an hours cell which adds them up for you.
BUT if someone is on a rest day or a holiday day or a sick day etc, I would like them to be able to type that in without the cell that adds the hours up going doo laly!!!!! (######)
So, is there anyway they can type say 'R' and then getting the hours total cell to ignore it because it isn't a time?
View 4 Replies
View Related
Jul 11, 2009
I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
View 14 Replies
View Related
Oct 14, 2009
I am making a user form in Excel 2007 and for some reason where I am using textboxes for a user to enter numeric values the output on the spreadsheet has a general format which is causing an IF function to return an incorrect value. Is there a way to make sure the output from the form is in "numeric" format?
View 10 Replies
View Related
Dec 31, 2009
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
View 9 Replies
View Related
Oct 11, 2008
I have a Col where the cells have been formatted to enter "PR" before the numbers input into any cell in that Col. I did this by using the custom selection in the format cell. However some times i need to input the number with the letter "A" at the end. For instance PR123456A.
Now in some cells it lets me do this but in others it does not. It would end up with 123456A, loosing the PR at the front. I have checked that all is well within the custom window. Can any body offer an explanation as to why it could be doing this.
View 2 Replies
View Related
Feb 5, 2014
I need to add some HH:MM values to give a total general value, its for a staff rota, i need total values for the hours worked per day/week.
eg. three 8 hour days in 08:00 format to give a total of 24
View 2 Replies
View Related
Jan 20, 2009
How Do I Convert Date:
10/3/2008 into October
6/1/2008 into June
7/3/2008 into July
I have date 10/3/2008 in cell and if I convert format to M into Just gives me first letter of Month, so O, I want it to read October, etc.
View 2 Replies
View Related
Dec 15, 2008
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
View 3 Replies
View Related
Oct 8, 2013
Recently I have been practising writing my own macros in VBA. I am using Excel 2010.
I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.
I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..
View 7 Replies
View Related
Mar 2, 2009
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies
View Related
Oct 24, 2008
Excel 2003
Lets see if I can even explain what I want to do.
will have a date column
Will have a total calorie column (this will be a sum of several column containing individual foods)
On a separate worksheet (food sheet) will be a list of foods in one column and their calories (per serving) In the next column.
On the first sheet I want a drop down that lists all the foods on the "food sheet). selecting from this drop down will use a vlookup (or hlookup - depending) to arrive at a caloric value.
So here is (are) the problems. (and the solutions need to be NON-VBA)
1. How do I tie the drop down selection to a vlookup (macro maybe)
2. How do I use that one drop down to populate any cell on sheet one?
can this be done without coding?
This will be an exercise for an excel class to teach vlookups, linked sheets,
making a dropdown, using a macro, formulas, IF statements............
The class is probably not ready for VBA which is why this needs to be a non-coded solution.
I am open to other solutions (related topic) that can bring together all of the above learning concepts
View 14 Replies
View Related
Dec 27, 2007
I have a LOG sheet where I am getting all my data from,
and a TIME and DUTY sheet..
COL A Has Dates, a4 to a34, Limited to 31 lines for the dates
and in col F I have # values, (1.2-2.2 etc).
The formula I have in Col F is
=SUMIF(LOG!$A:$A,#REF!,LOG!F:F)
but when i get to the end of the month, say for example the last 2 lines of the time and duty sheet, when a32 is 29/ Feb, the next row is blank and same with next row. (leap year).
How do I get the formula above to ONLY calculate when the cell in COL a on the same row, when there is a DATE value in COL a?
View 9 Replies
View Related
Jan 20, 2009
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 Excel 2003 and 2007
View 10 Replies
View Related
Dec 12, 2009
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies
View Related
Mar 8, 2012
I am currently using the .resize function to give a specific value to each cell within a range.
First of all, is it the most efficient way to do it?
If so, why am I getting error "Application-defined or object-defined error"
with this code :
Code:
ThisWorkbook.Sheets("Merge").Range(Cells(3, 2), Cells(4000, 2)).Resize.Value = "TEST"
View 2 Replies
View Related
Dec 6, 2009
-In cells J6:P11
- Display a MsgBox for the value in J6; Then
- Display MsgBox for the value in K6; Then
- Display MsgBox for the value in L6
- etc, etc
View 7 Replies
View Related
Feb 2, 2014
i am trying to return the numerical characters from a cell so in A1 i have 6-4 3-6 7-5
A2 i would like to return 6
A3 4
A4 3
A5 6
A6 7
A7 5
there are also possibilties that A6 & A7 could b 2 digit numbers such as 10-12 or could be 9-11 or 11-9 etc
View 4 Replies
View Related
Apr 23, 2006
I'm working with a fairly large spreadsheet, and need to find out how many times a specific word or numerical value comes up in a column (for instance, I might need to know how many times 'Anderson' comes up in column B). Every time I try to use the sum, dsum, sumif, count, dcount, or countif functions, however, I keep coming up with either 0 or the #VALUE message. This is very frustrating, as you can well imagine. I could try to sort the data by the various terms and values I need and then physically count everything but this strikes me as time-consuming, prone to error (we're talking a few thousand entries, here), and something Excel should theoretically be able to do anyway if only I knew how to ask it.
View 2 Replies
View Related
Jun 16, 2007
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.
View 4 Replies
View Related
Mar 7, 2014
I need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.
So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter
View 3 Replies
View Related
Mar 5, 2009
I am trying to put together an IF statement within excel which will give me certain output depending on the particular value within ONE cell.
For example:
If cell L5 has a value between 0 and 1.00 output value 0
If cell L5 has a value between 1.01 and 2.00 output value 20
If cell L5 has a value between 2.01 and 3.00 output value 40
If cell L5 has a value between 3.01 and 4.00 output value 60
If cell L5 has a value between 4.01 and 5.00 output value 80
If cell L5 has a value between 5.01 and 6.00 output value 100
View 4 Replies
View Related
Oct 7, 2008
In a single cell, I would like to show 2 values: a currency and a percentage. So A1 and A2 has values, say 50000 and .10 respectively (these are derived via formula, if it's of any relevance). What I ultimately want is "$50,000 and 10%". I've originally thought about adding the "$", " and ", and "%" separately, but I need the currency value to include commas.
View 2 Replies
View Related
