How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1917
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I AM LOOKING FOR A FORMULA TO CONVERT A LETTER TO A SPECIFI

This answer was rated:

I AM LOOKING FOR A FORMULA TO CONVERT A LETTER TO A SPECIFI NUMBER.

EXAMPLE: In horses a ringer (R) is 3 points and a Leaner (L) is 2 points
How can I put it in a excel sheet to say R or L is added to the total

The-PC-Guy :

Hi, My name is Andrew

The-PC-Guy :

I can help you with this

The-PC-Guy :

can you provide the formula you have so far?

Hi, My name is Andrew

9:31 PM

I can help you with this

9:31 PM

can you provide the formula you have so far?

hi, you seam to have been disconnected.

I need a little bit more information

are you saying tha you have a column that has Letters and Numbers in it and you want to sum the numbers and letters?
Customer: replied 3 years ago.


I can send you the excel sheet with the information in it and what the calculations answer should be

that would work.

if you have a paperclip icon at the top of your chat window you can try clicking that. iT says image, but works on excel files
if that does not work

try

www.wikisend.com

just upload the file there

they will then give you a file ID#

paste that ID# XXXXX
Thank you for your question, my name is Richard.

I believe what you want to use is this formula

In this example, we are checking Cell A1

If(A1="R",3,if(A1="L",2,0))

So in this example, if R is in A1 you get a value of 3. If L is in A1 you get a value of 2.

We can expand this if you wish.

If you have any difficulties at all, please do not hesitate to let me know so I can assist you further

Thank you

Lets assume that the column you want to sum is the D column and it contains letters and numbers.So assuming you want to sum cells D1 - D25, the following formula would help you

 

=SUM(D1:D25)+COUNTIF(D1:D25,"R")*3+COUNTIF(D1:D25,"L")*2

 

the way this works is the first part sums only the numbers, the second part counts all of the Rs and multiplies those by 3 and adds to previous total, the 3rd part does the same accept for the Ls

 

You can change the ranges if you need to.

 

Remember that the range (D1:D25) in this example is in 3 places in the formula, to apply it to your range, you would need to change in all 3 places to be the same range.

 

Let me know if you have any problems with this formula or need my help further

Customer: replied 3 years ago.

very close


Can I also do the same for a double ringer RR, Leaner LL


Also can a ringer plus closest to the pin work R1


(3 points for the ringer and 1 point for the other shoe?)


 


LL=4 points


RR=6 points


R1=4 points


L2=4points


 


So far everything is very close


Do you need me to send the excel sheet?


That way you can see what I am talking about?


There are 17 columns and 4 rows on the score sheet


4 players


 

yes, sending the sheet would be the best way to accomplish this for you.

www.wikisend.com

just upload the file there

they will then give you a file ID#

paste that ID# XXXXX

alternativelly you can try

www.ge.tt

works similarly, they give you a download link

paste that link here
you can also add any criteria you want to the formula

so

=SUM(D1:D25)+COUNTIF(D1:D25,"R")*3+COUNTIF(D1:D25,"L")*2

if you want to add LL=4 points in you would just add

+COUNTIF(D1:D25,"LL")*4

so the new formula would look like

=SUM(D1:D25)+COUNTIF(D1:D25,"R")*3+COUNTIF(D1:D25,"L")*2+COUNTIF(D1:D25,"LL")*4

if you want to add

R1=4 points

+COUNTIF(D1:D25,"R1")*4

so the formula would be

=SUM(D1:D25)+COUNTIF(D1:D25,"R")*3+COUNTIF(D1:D25,"L")*2+COUNTIF(D1:D25,"LL")*4+COUNTIF(D1:D25,"R1")*4

and so on for as many criteria as you need.

but if you can get the sheet to me. I can do the formulas for you.

Whatever you prefer
Customer: replied 3 years ago.


WIKISEND FILE ID# XXXXX


Column U has what should be the correct answers.


Let me know


Thanks

ok i got it

what are the values supposed to be for

H
RH
H1
here is my solution.

I had to guess at the H, RH and H1 values because you didn't get back to me, but you can change the values to suit your needs.

http://ge.tt/3rrTn0u/v/0?c


PLEASE REMEMBER TO RATE MY SERVICE SO I AM REIMBURSED FOR MY TIME

if you need anything else let me know
they will not let us email

but here is the formula for cell T3

let me know if it cuts off

=SUM(C3:S3)+COUNTIF(C3:S3,& quot;R")*3+COUNTIF(C3:S3,"H")*2+COUNTIF(C3:S3,"RH")*5+COUNTIF(C3:S3,"LL")*4+COUNTIF(C3:S3,"RR")*6+COUNTIF(C3:S3,"R1")*4+COUNTIF(C3:S3,"L2")*4+COUNTIF(C3:S3,"H1")*3

here is a text file containing the formula, cant possibly have a virus. And by the way the other file I sent didn't have a virus. That was a problem on your end

http://wikisend.com/download/842392/formula.txt
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1917
Experience: 20 years experience providing remote computer support
The-PC-Guy and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.


Virus not here just AVG picked it up as one.


Anyway the last formulas worked out great


Thanks,

no problem, I am glad everything worked out

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Related Microsoft Office Questions