what sort of formula...

  • Thread starter Thread starter John
  • Start date Start date
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
 
Do you want to average the numbers in the range A1:C3?
use =AVERAGE(A1:C3)
best wishes
 
but with the addition a b or c excel can't average these.. is there a work
around for this?
 
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?
 
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?
 
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
 
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
Click to expand...

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

hth RES
 
cheers... could have been me.. sorry!

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

my solution as posted is

={"1a","1b","1c","2a","2b","2c","3a","3b","3c","4a","4b","4c","5a","5b","5c"
}

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

hth RES
Click to expand...
 
Back
Top