Hi,
I have several letters that need to have a numerical equivalent. These are:
F=0, P=1, M=2, D=3
In cells A1:E1 there could be a combination of F's, P's M's and D's, eg
P M D D P
This would equate to 1 + 2 + 3 + 3 + 1 = 10 or an average of 10 / 5 = 2.
Any help with creating a sum & average formula to achieve this would be greatly appreciated.
Try:
=SUM(LOOKUP(A1:E1,{"D","F","M","P"},{3,0,2,1}))
and
=AVERAGE(LOOKUP(A1:E1,{"D","F","M","P"},{3,0,2,1}))
Confirmed with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks NBVC - that worked a charm
Sorry NBVC I have spotted a potential pitfall to the solution you have offered, namely that there could be 0 - 5 values in that range, ie sometimes it is blank, sometimes only 1 value, say a P or a few. The formula returns #N/A when there are less than five values present in the range. Is there any way
a) For the formula to return empty is 0 values are present
b) For it to calculate if 1 or more letters are present in the range?
Thanks again.
Last edited by Marie1uk; 09-05-2008 at 03:50 PM.
Do you want to average say 4 cells if 4 letters are present?
If yes, then try:
=SUM(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"D","F","M","P"},{3,0,2,1})))
and
=AVERAGE(IF(ISTEXT(A1:E1),LOOKUP(A1:E1,{"D","F","M","P"},{3,0,2,1})))
again confirmed with CSE keys.
Again, thanks for this and jumping to the right conclusion despite my wafflings
Hi,
this information really helpful to me. I have to calculate student average of large dataset in excel. Like to follow this method.
But it includes several characters such as A+ = 4.25, A = 4, A- =3.75 and so on.
And the above method is not working for it. is there any solution for that.
Welcome to the forum,
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks