# Allocate Numbers To Letters And Find Average

May 7, 2009
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).

View 9 Replies
ADVERTISEMENT
Jan 27, 2012

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.

View 9 Replies
View Related
Nov 27, 2008

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.

View 7 Replies
View Related
Jul 29, 2014

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.

View 13 Replies
View Related
Jul 24, 2012

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.

View 3 Replies
View Related
Feb 13, 2012

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,"

View 2 Replies
View Related
Feb 25, 2007

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)

View 14 Replies
View Related
Feb 11, 2008

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 Related
Jan 21, 2012

Using 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?

View 5 Replies
View Related
Aug 28, 2006

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.

View 9 Replies
View Related
Jun 14, 2013

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.

View 14 Replies
View Related
Feb 26, 2014

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.

View 4 Replies
View Related
Jan 16, 2014

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 Related
Apr 17, 2009

This 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

View 9 Replies
View Related
Jul 8, 2014

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

View 9 Replies
View Related
Aug 21, 2014

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

View 6 Replies
View Related
Jan 23, 2014

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

View 3 Replies
View Related
Apr 5, 2007

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

View 13 Replies
View Related
Oct 26, 2009

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 Related
Oct 7, 2010

How 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

View 14 Replies
View Related
Jan 30, 2013

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 Related
Feb 13, 2013

How to autofill numbers with letters in excel 2007. Eg. 74cm, 80cm so on....?

View 4 Replies
View Related
Aug 8, 2013

Is 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 Related
Jun 26, 2007

We 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.

View 9 Replies
View Related
Jan 4, 2008

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

View 9 Replies
View Related
Oct 23, 2009

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.

View 9 Replies
View Related
Feb 15, 2007

How do I give letters a value in Excel? i.e. How can I make A = 1

View 9 Replies
View Related
Jun 30, 2008

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 Related
Feb 7, 2007

How can I avoid letters in a cell (read only numbers)

View 11 Replies
View Related
Jan 25, 2009

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)?

View 6 Replies
View Related