Entering Text and having it represent a value

Posted by Chris on September 21, 2001 10:14 AM

I would like to be able to have my users enter direction lik N S E W for north south east and west. Later I need to average all the data. How do I have a "w" mean 270 to excel?
I was trying
If Cell(x,5) = "w" then cell(x,5).value = 270
Abviously it doesn't like that.
Is my Syntax wrong or is there a better way to do this?

Posted by Mark W. on September 21, 2001 10:45 AM

I presume you mean VBA and not Excel. (nt)

Posted by IML on September 21, 2001 11:03 AM

Just a guess, but assumming your data is in A1:A10, you could try
=(COUNTIF(A1:A10,"N")*0+COUNTIF(A1:A10,"e")*90+COUNTIF(A1:A10,"s")*180+COUNTIF(A1:A10,"s")*270)/COUNTA(A1:A10)
otherwise you could use a nested if statement or vlook up such as
=VLOOKUP(A1,{"n",0;"e",90;"s",180;"w",270},2,FALSE)
I assumed north = 0, east 90, s 180,w 270

good luck up