what sort of formula...

J

John

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
 
B

Bernard Liengme

Do you want to average the numbers in the range A1:C3?
use =AVERAGE(A1:C3)
best wishes
 
J

John

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

comish4lif

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

John

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

comish4lif

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

John

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

comish4lif

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

John

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
 
Top