formula to do the following:
Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter.
So in a 5 cell range the values could be:
blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).
I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.
The row may therefore have cells that either have numbers or letters.
Column A will be the title of the row.
I want to write a function to find the average value of the numbers in any row with title 'x'.
So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.
I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.
The sheet looks something like this, numbers are all in one row:
7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
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.
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.
I am using excell 2007 and 2010
I need to find the average numbers on sheet 1 column D. Column D contains numbers as well as Blank cells. The info will be presented on sheet 2 in a monthly view. In other words i need to ssearch awithin a date range and determin the average of those numbers. This works on some months but return a DIV/0 erro most of the time.
=AVERAGEIFS('2N Stats'!$I:$I,'2N Stats'!$E:$E,">=2/1/2011",'2N Stats'!$E:$E,"
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)
I have a table with 9 columns filled by a letter (A, A-, B+, etc). This letters correspond to grades (4, 3.67, 3.33, etc). The tenth column is supposed to contain the numerical average of all 9 letters in the line.
View 9 Replies View RelatedUsing the following data
R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8
I want it to sort like this:
R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
What is the formula to achieve this?
I'm doing a course at the mo and get graded monthly. I have a spreadsheet with my grades on 'assignment1, grade A, assignment2, grade B and so on.
What i want to do is have a cell that has the aveage of all grades. So if i had...
Grade A
Grade B
Grade C
the average is B.
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
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.
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 RelatedThis 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
I'm working on a function that will sum a row of cells containing letters and numbers. I am currently using :
=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:S5,"T",""),"TP",""),"TPE",""),"P",""))+0) as my base.
Right now the formula will work with T, TP, and P; however as soon as I type TPE I get #value.
Either way you get the point of what I am trying to do, however my cells can contain any one of the following combinations:
T,TP,TPE
P,PE,
E
In column A I have the following numbers
13710
14782H
9827
14782
14206B
a formula that looks at this range of cells and returns only the cells that have a letter at the end. The letter range goes A to M
I am a school teacher trying to adjust my tracking sheet to calculate pupils levels. I am looking for 2 potential formulas that will do the following.
1 - In cell AE I would like to generate a formula that will take the data entered in cells J:5, L:5, N:5, P:5, R:5, T:5, V:5, X:5, Z:5, AB:5 and AD:5 and give an average level.
2 - In cell AH is it possible to generate a formula that will calculate how many levels of progress the pupils are making - In other words I need Cell I to be calculated against cell J to see how much progress the pupils are making - for example if in cell I:5, a pupil is was given a 3a, and then in cell J:5 is given a 4b, they will have made 2 sub levels of progress. As well as this, can that progress then be averaged out across cells I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to give an overall number of of levels of progress? An then..... can I colour co-ordinate the cell so that if the pupils are making 3 or more sub levels of progress it turns green, 2 sub levels orange and 1 sub level red?
Levels work like this
3c
3b
3a
4c
4b
4a
5c
5b
5a and so on
i want to convert numbers to letters in the excel Sheet
I.E: when the number is 1000.00$ it write in another cell
on thousand dollar only
1500.00$ one thousand five hundred dollar only
I have a word, for example ROCK. I asigned numbers to the word. R=1,O=2,C=3,K=4. My Question: If I type in ROC it must return 123. If I type in KR it must return 41, etc. Is there a basic formula I can use to solve this?
View 4 Replies View RelatedHow do I do data validation/cell formatting so that only alphabetic characters can be entered and no numbers?
(We use the cell for First Name, Last Name, etc. and I don't want potential entries like Mr. 56565 55454 because the cells are formatted as text and allow alphabet and numbers.)
I tried =NOT(ISNUMBER(H6)) for Data Validation, but that didn't work.
??
VR/Lost
I have in cell A1 the number 2 and in cell A2 the letter m.I want to add these two cells in cell A3 so as to appear 2+m.So i type in cell A3 =A1+A2 but it appears error.Is there a way to do what i am asking?
View 3 Replies View RelatedHow to autofill numbers with letters in excel 2007. Eg. 74cm, 80cm so on....?
View 4 Replies View RelatedIs there any formula or macro which gives the result from numbers to letters..? say like; 2,471,803.40 is in number and I'm looking for the result as 'RUPEE TWO MILLION FOUR HUNDRED SEVENTY ONE THOUSAND EIGHT HUNDRED THREE AND FORTY PAISE ONLY'. Is it possible to create macro to get this type of result..?
View 9 Replies View RelatedWe are doing a database conversion and I have been tasked with converting all our old promotion codes to a new system. The long and the short of it is this:
I am trying to write an equation that will look at two cells (say A1 & B1) which when put together could be one of the following numbers (01,02,03,04,05,06,07,08,09,10,11,12) and translate them to a letter (A,B,C,D,E,F,G,H,J,K,M,N)
So 01=A, 02=B and so on...
I was trying to use if statements, but there are too many.
I have a list I want to sort containing both letters and numbers. Right now the regular sort sorts like this
ABC-1
ABC-11
ABC-12
ABC-2
I want it to sort like this and don't have a clue how to do it
ABC-1
ABC-2
ABC-11
ABC-12
I am using excel 2003
I have an excel sheet where the user enters a bunch of info. In one of the cells, they enter an "account code" - this is then used to SAVE AS the account code.
The problem... if they enter things into this cell that causes the filename to fail (eg: *, /), the code errors out. I can error handle this in VBA at the time of the save, but I would rather prevent them from entering anything that is not a letter or number in the first place.
How do I give letters a value in Excel? i.e. How can I make A = 1
how to make letter represent numbers what i am trying to do. i am trying to put a sheet together to make my life easyer. i am a lectuer and need to keep trake of studes progress how this is done is that over the year they are graded my Pass Merit Distinction in using offic 2007 (Excel)and i was to make it so that if i put in a P it will =6 points ,M=12 points and D=18 points and for the total points to for each student to come up on the side after the units if there a rule for this and how would i put the rule into the sheet ?
View 3 Replies View RelatedHow can I avoid letters in a cell (read only numbers)
A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.
Example:
xxxx1
yyy3yyyy1
xyzxx1yxz4xzx1
xxx2xxx2xxx2xxx2
What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:
xxxx1
yyy3 and yyyy1
xyzxx1 and yxz4 and xzx1
... and so on
Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?