EXCEL question

[Cheesypuff]

I have a question for you peoples!!!

So I'm using excel, and I'm wondering if there is a function in excel that lets me input a letter into a cell, but outputs a number. Please TELL ME HOW TO DO SO!!! need massive help with this project!!!

[ray]

so let's say you enter B, you want that same cell to show the number 5?

[Markel]

If you input "A" into A1 and in B1 you have the formula =CODE(A1) you will get a 65 in B1.

[ski]

If you input "A" into A1 and in B1 you have the formula =CODE(A1) you will get a 65 in B1.
From there, I'm guessing "B" is 66, etc.? It might be a MOTO thing to say, but if you want it to be in the simplest code, just do =CODE(A1) - 65 to get A to be 1, B to be 2, etc.

[Cheesypuff]

yeah thats what I'm going for...but the thing is...with putting an equation in the cell, and refering it back to that cell, it would make it circular equation.

it has to be user friendly, so I have to make it so the user inputs a Letter, and a number comes out.

this is what I need

E=1, D=4, C=3, B=4, A=5

the person inputing the information is reading straight off a paper, and putting it on the excel Spread Sheet. So to reduce error, I need to have it so the output in a number. The reason I need numbers there, is so that I can calculate stats from those numbers.

[Fas-ligand]

how about function SUBSTITUTE.

[Cheesypuff]

I've tried that one...but it won't work since it relies on text that is currently there!

anywho...I think I found a way around my problem! So I can move on....

but I have another question...

say I wanted to do a weighted average.

which means, I've taken the averarage of a list of numbers...but there are some outliars that are skewing the information.

problem, my average is 1.75, and any numbers of my list that are 1.68 plus or minus away from my average I want to not count it towards my weighted average. How to accomplish this matter?

[Cheesypuff]

edit...screw that last question I have

I'm using the countif function on excell...but the thing is, the if criteria doesn't accept cell references...does anybody know of another function that does a count if that accepts cell references!!!!

[GilbertsGrape]

why dont you just jet them enter all the data tehn use the find and replace feature of excel then do your calculations

[ramazank2]

for your first question you can use IF... THEN...

[Cheesypuff]

AHHA...I found out that the function COUNTIF does use cell reference

you can bust it out like so... =COUNTIF(">"&A1)

friggin excell!!!!