PC Review


Reply
Thread Tools Rate Thread

what sort of formula...

 
 
John
Guest
Posts: n/a
 
      19th Jul 2005
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


 
Reply With Quote
 
 
 
 
Bernard Liengme
Guest
Posts: n/a
 
      19th Jul 2005
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
>
>



 
Reply With Quote
 
John
Guest
Posts: n/a
 
      19th Jul 2005
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
> >
> >

>
>



 
Reply With Quote
 
comish4lif@verizon.net
Guest
Posts: n/a
 
      19th Jul 2005
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?

 
Reply With Quote
 
John
Guest
Posts: n/a
 
      19th Jul 2005
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?
>



 
Reply With Quote
 
comish4lif@verizon.net
Guest
Posts: n/a
 
      19th Jul 2005
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.

 
Reply With Quote
 
John
Guest
Posts: n/a
 
      19th Jul 2005
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.
>



 
Reply With Quote
 
comish4lif@verizon.net
Guest
Posts: n/a
 
      19th Jul 2005
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?

 
Reply With Quote
 
John
Guest
Posts: n/a
 
      20th Jul 2005
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?
>



 
Reply With Quote
 
Robert_Steel@nothanks.com
Guest
Posts: n/a
 
      20th Jul 2005
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Want to sort by formula value without losing formula HappyPill Microsoft Excel Misc 4 22nd Sep 2010 06:59 PM
Formula to sort kix Microsoft Excel Misc 3 1st Feb 2010 07:39 AM
SUM formula (sort of) cjones4mvp Microsoft Excel Worksheet Functions 1 9th Oct 2009 05:30 PM
Ascending Sort formula, change to neg #: descending sort.. =?Utf-8?B?bmFzdGVjaA==?= Microsoft Excel Misc 6 3rd Jul 2007 12:00 AM
How can I sort by the value of my formula and not actual formula =?Utf-8?B?TWFy?= Microsoft Excel Worksheet Functions 1 26th Oct 2005 10:51 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:06 PM.