i have a set of numbers 1a 1b 1c through to 3a 3b 3c etc... is there a
formula that would work out an average?

i.e. 2b 2b 1a might average out to about 2ish

maybe changing the a b c for something else?
not maths bod...!

John

Do you want to average the numbers in the range A1:C3?
use =AVERAGE(A1:C3)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John" <(E-Mail Removed)> wrote in message
news:IG7De.1888$(E-Mail Removed)...
>i have a set of numbers 1a 1b 1c through to 3a 3b 3c etc... is there a
> formula that would work out an average?
>
> i.e. 2b 2b 1a might average out to about 2ish
>
> maybe changing the a b c for something else?
> not maths bod...!
>
> John
>
>

but with the addition a b or c excel can't average these.. is there a work
around for this?

"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Do you want to average the numbers in the range A1:C3?
> use =AVERAGE(A1:C3)
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "John" <(E-Mail Removed)> wrote in message
> news:IG7De.1888$(E-Mail Removed)...
> >i have a set of numbers 1a 1b 1c through to 3a 3b 3c etc... is there a
> > formula that would work out an average?
> >
> > i.e. 2b 2b 1a might average out to about 2ish
> >
> > maybe changing the a b c for something else?
> > not maths bod...!
> >
> > John
> >
> >
>
>

Tell me again the mathematical basis for averaging letters?

Or do you just want to average the number part?

Is 2A like 2.3 and 2C like 2.7?

i know it sounds stupid... it is... ok this is what i've come up with..
a list of all my codes - 1a 1b 1c 2a 2b 2c etc and assigning a number for
each 1a = 1 1b = 2 1c = 3 2a = 4 etc. Averaging these should be fine now.

Now... how do I get excel to display say 2b instead or 5 i.e. 5 = 2b?



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tell me again the mathematical basis for averaging letters?
>
> Or do you just want to average the number part?
>
> Is 2A like 2.3 and 2C like 2.7?
>

If you have a table where you can store the 2B=5 part, you can do a
vlookup. The vlookup will search the table to find the 5 (your average)
and return the 2B.

cheers- this looks just the job.. shame my poor excel skills won't let me
get it working!
I have on a worksheet in column A numbers 1 - 24 and in B 1ba 1b 1c etc.
Just not sure how to point the formula in the right direction.
Actually if it isn't confusing matters what i'd really like to be able to do
is type say 1a, have excel convert that to 1 then average it then return the
code.
i.e.

2b 2b 2b

becomes
5 5 5

then averaged and return 5 in the next column.

Too hard for me? or just make no sense?

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you have a table where you can store the 2B=5 part, you can do a
> vlookup. The vlookup will search the table to find the 5 (your average)
> and return the 2B.
>

It would help to know exactly what you are trying to do.... rather than
your interpretation of how to do it, nevertheless, I press on.

PLace this Formula in E1:
=VLOOKUP(4,$A$1:$B$24,2)

Where D1 represents the number (1-24) that you wish to lookup. And
$A$1:$B$24 represents the two columns that is storing your "lookup"
values, 1-24 in column A and 1A-8C in column B, the "2" says to pull
the answer from the 2nd column to the right of finding the value
closest to the value in D1. The $ signs keep the lopokup array from
"moving" with the formula. If you were to copy the formula down a row
of cells, $A$1:$B$24 will remain the same. Without the $ signs, A1:B24
becomes A2:B25 then A3:B26.

That help?

yeah loads... I just really need to do some excel courses! It really will do
everything you want.. just very hard to see what to do and how to ask!
Thanks
John

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It would help to know exactly what you are trying to do.... rather than
> your interpretation of how to do it, nevertheless, I press on.
>
> PLace this Formula in E1:
> =VLOOKUP(4,$A$1:$B$24,2)
>
> Where D1 represents the number (1-24) that you wish to lookup. And
> $A$1:$B$24 represents the two columns that is storing your "lookup"
> values, 1-24 in column A and 1A-8C in column B, the "2" says to pull
> the answer from the 2nd column to the right of finding the value
> closest to the value in D1. The $ signs keep the lopokup array from
> "moving" with the formula. If you were to copy the formula down a row
> of cells, $A$1:$B$24 will remain the same. Without the $ signs, A1:B24
> becomes A2:B25 then A3:B26.
>
> That help?
>

See a similar post in the Formula group
subject - formula problem - UK national curriculum levels

my solution as posted is

>Firstly define a Name with a standard list of grades
>eg, Grades
={"1a","1b","1c","2a","2b","2c","3a","3b","3c","4a","4b","4c","5a","5b","5c"}
>
>then the average grade from a range is
>=INDEX(Grades,ROUND(AVERAGE(MATCH(A1:A10,Grades,0)),0)) array entered -
>control shift enter
>
>change A1:A10 to reference a list of grades

however there are a number of options there from far more experienced
users

hth RES