Excel: Converting Text to value 1

[hewissa]

I am creating a workbook in Excel. I need to be able to convert text - "x" to a number "100". On sheet 1 the "x" is placed in a cell indicating whether or not a condition is met. No other letters are used. On sheet 2 I need to average the "x's" and represent the outcome as a number from 1 to 4.


In sheet 2 I use the formula - Average(sheet1;A1..D1)IF(A1=100,4)

If sheet1 A1 is "x", then how/where do I specify that it equals a value of 100?



Hewissa

MCSE, CCNA, CIW

 

[Zathras]

I don't completely understand your question, but this formula can be used to count the number of x's. You could then multiply by 100 or do whatever else you need:

  =COUNTIF(Sheet1!A1:A4,"x")

 

[hewissa]

Not quite what I am looking for, thanks Zathras.

More like this:

Sheet1
A B C D
Person Criteria Criteria Criteria
Joe x x x


Sheet2
A B C D
Person Qualified
Joe Average(sheet1;A1..D1)IF(A1=100,4)

The result for cell B2 would equal 4

I hope that helps. Let me know if more clarity is needed.


Hewissa

MCSE, CCNA, CIW

 

[Zathras]

I'd like to help, but I don't understand what you are trying to express with

  Average(sheet1;A1..D1)IF(A1=100,4)

It is meaningless in Excel.

It would seem to be saying that

  ("Joe" + "x" + "x" + "x")/4 = 100

but the condition

  IF(A1=100,4)

would always return FALSE since in your example, A1 = "Person"

Perhaps if you could provide a few more examples of input data along with the desired results someone could make sense of it.

 

[hewissa]

"...the condition IF(A1=100,4)would always return FALSE since in your example, A1 = "Person"...correct, the cell should be B1

Maybe to may 100's in the example, how about this:

Sheet1
A B C D
Person Criteria Criteria Criteria
Joe x x


Sheet2
A B C D
Person Qualified
Joe Average(sheet1;B1..D1) IF(B1<75,3)


The &quot;x&quot; would represent 100. That is the conversion part I require, how to convert that &quot;x&quot; to a value ammount of 100. Therefore the formula would be:

=Average(sheet1;A1..D1) IF(B1<75,3) Or would be executed like this:

=Average(Sheet1;100+100+0)/3 IF B1<75 show 3


Is there a way to have Excel understand the the value of &quot;x&quot; equals 100?

Confusing indeed...I can't see the forest through the trees...

I appreciate the help none the less.



Hewissa

MCSE, CCNA, CIW

 

[Zathras]

So, with three x's you want a result of 4 and with two x's you want a result of 3?

Then why can't you use:

   =COUNTIF(Sheet1!B2:D2,"x")+1

(A slight variation from my first answer.)

 

[vickero007]

I don't understand IF B1<75 show 3. Do you mean B2? Like if the average is less than 75 show 3 but if it's above show 4?

-Volkoff007

 

[hewissa]

You are correct. It works, up to 4. But how do you refelect a count of 7 &quot;x's&quot; as 4? The Qualified count needs to amount up to 4.

If I count 8 cells, and 7 have &quot;x's&quot; the number shown needs to be 4 - 4 being highest.

If I have 8 cells and count 3 &quot;x's&quot; I'll need to represent that as 1 - 1 being lowest.

Thats why I thought if I converted it to 100's and averaged them I could apply the value better.

Your thoughts?






Hewissa

MCSE, CCNA, CIW

 

[hewissa]

Volkoff007,

No, IF B1 - the calculation is in C1 figuring the result of B1. I haven't figured that part out yet as to if it is higher show 4 or lower show 2.

I haven't got my head around the text to value issue yet....

Hewissa

MCSE, CCNA, CIW

 

[vickero007]

I assume you want to average the number of x's out of the total possible and then assign a 0-4 rating like this:

average = #ofx's/totalpossible

If average > or = 75 then rating = 4
If average > or = 50 then rating = 3
If average > or = 25 then rating = 2
If average > or = 1 then rating = 1
If average = 0 then rating = 0

If that is correct I created a VBA function that can do that for you. You can download the excel file I used to test it from here:

http://mywebpages.comcast.net/mrbcstudents

Click the link for Book1.xls and dowload it
When prompted to enable macros choose enable

Just update the x's as you like and hit the button to calculate the ratings which will be on Sheet2

To edit/view the code:

In Excel make sure the VB toolbar is showing (View>>Toolbars>>Visual Basic)

Click the icon to the left of the hammer&wrench. It's a white window with a light blue title bar.

You should now be looking at the code, but if not double click Sheet1 on the left panel.

I put a ton of comments in so you can update the settings as needed, just save the file in the excel screen when you are done.

Let me know if you have any problems or would like me to tweak it further.

-Volkoff007

 

[hewissa]

Awesome, exactly what I wanted.

I am not very familiar with VB code, but your comments are self explanitory. Easy to follow!

I did have a question. On sheet2, I have different areas of Qualification &quot;headers&quot;. B1 being Quality# and C1 Quality# and so on. Each Quality references different consecutive cells in Sheet1. How do I calcualte that?

Hewissa

MCSE, CCNA, CIW

 

[vickero007]

Okay, I'll need help understanding this.

In the file I gave you there were 8 Criteria.
There was only 1 Quality.

Do you want different qualities to represent different combinations of Criteria?

For instance:

Jude had the first 5, so if Quality1 is based on CriteriaB-D then he got 3/3 = 100 = 4

However if Quality2 is based on G-I then he got 0/3 = 0 = 0

Is that what you mean?

If so tell me how many Criteria you will have and what the relationships are like 1-3 are Quality1 and 4-5 are Quality2...etc.

I'll update it so each Quality is graded and the total (like now) is graded also.

Basically instead of just xcounter I'll have quality1counter, quality2counter ..etc and totalcounter so each quality can be posted and totalcounter will be the sum of the qualitycounters. Then I'll put that last section of If-ElseIf's in a For loop so it does it for each Quality.

-Volkoff007

 

[hewissa]

Yes that is what I mean. I don't have the cell/criteria/quality ratio information at the moment.

I will post the info you requested.

Greatly appreciated!

Hewissa

MCSE, CCNA, CIW

 

[hewissa]

volkoff007,

The worksheet corelation goes like this:

Cells Sheet1 related to Cells Sheet2
C,D B
E,F,G,H,I C
J,K D
R,S,T,U E
AA-AI F
Q,V,W,X,Y,Z G
L,M,N,O,P H
AJ-AT I
AU-AY J
BH,BI L
BE-BG M
AP,AS,AT N

I appreciate you looking at this for me. Maybe with some explanation you could do the first few and I'll do the rest. Then if I run into trouble I can bounce it back to you.

Let me know.


Hewissa

MCSE, CCNA, CIW

 

[vickero007]

Hewissa,

I updated the file to reflect your relationships. I renamed it Book2 in case you want to be able to refer back to Book1 for code. Just a note, if you open both Book1 and Book2 you will have 2 separate excel windows but only one VB window. The VB window will have both Books in it and sometimes that can get confusing.

I'll leave the rigorous testing up to you. Once you rename the headings you should test it out to make sure there are no mistakes. Test each &quot;Quality&quot; individually to make sure the results show up in the correct column in sheet2.

Have fun,

-Volkoff007

 

[hewissa]

Thanks, really appreciate it!

I'll keep you posted. Ok to get back to you if I have questions?

Hewissa

MCSE, CCNA, CIW

 

[vickero007]

I should be checking the forums regularly, post anything in this thread if you have a problem/question.

-Volkoff007

 

[hewissa]

volkoff007,

Book1 is only available on your site. Book2 is not there.

You can email it to me if you wish hewissa@attbi.com or post it on your site and I'll grab it.

Thanks,

Hewissa

MCSE, CCNA, CIW

 

[hewissa]

volkoff007,

Book1 is only available on your site. Book2 is not there.

Thanks,

Hewissa

MCSE, CCNA, CIW