X= a value in a colum

[riley76]

I am creating a spreadsheet to grade a persons performance. There is Unacceptable(.1), unsatisfactory(1.54), below average(.23), average(.31), excellent(.35), and outstanding(.39). I want to be able to put an "X" in the box and have its value sent to the score colum on the end. I listed the value beside the rating above. I have no idea how to accomplish this. As of now I have to type the value in the box and have it autosum to the score colum. any help would be apprciated. I could also email the spreadsheet if someone wants me to.

 

[yogeshmaney]

http://www.filefactory.com/file/c039999/n/Book2.xls

Formula highlighted.

 

[riley76]

Thank you so much! Unfortunately my work blocks the website you posted can I get you to paste it here?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Jonmo1]

Welcome to the board...

Create a lookup table (A1:B7 below)
Then use a lookup formula (E2:E10 below)

Excel Workbook
	A	B	C	D	E
1	Score	Text		LookupVal	Result
2	0.1	Unacceptable		0.1	Unacceptable
3	0.154	Unsatisfactory		0.15	Unacceptable
4	0.23	Below Average		0.2	Unsatisfactory
5	0.31	Average		0.25	Below Average
6	0.35	Excellent		0.3	Below Average
7	0.39	Outstanding		0.35	Excellent
8				0.4	Outstanding
9				0.45	Outstanding
10				0.5	Outstanding
Sheet1

 

[Scott Huish]

This is what yogeshmaney posted:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	Score	Unacce	Unsatis	AvegExceOutS
2			0.1	0.154	0.23	0.31	0.35	0.39
3	A	0.154		x
4	B	0.31				x
5	C	0.39						x
Sheet1

 

[yogeshmaney]

Thanks "Hotpepper". Still figuring out how to paste it. I am not a pro.

 

[riley76]

Thank you so much I will try and get this to work in my spreadsheet!

 

[markmzz]

Or try this in H3 - array formula (use Ctrl+Shift+Enter and not only Enter):

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">0.10</TD><TD style="TEXT-ALIGN: center">1.54</TD><TD style="TEXT-ALIGN: center">0.23</TD><TD style="TEXT-ALIGN: center">0.31</TD><TD style="TEXT-ALIGN: center">0.35</TD><TD style="TEXT-ALIGN: center">0.39</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Persons</TD><TD style="TEXT-ALIGN: center">Unacceptable</TD><TD style="TEXT-ALIGN: center">Unsatisfactory</TD><TD style="TEXT-ALIGN: center">Below average</TD><TD style="TEXT-ALIGN: center">Average</TD><TD style="TEXT-ALIGN: center">Excellent</TD><TD style="TEXT-ALIGN: center">Outstanding</TD><TD style="TEXT-ALIGN: center">Score</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Person01</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">1.89</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Person02</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">2.69</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Person03</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">0.80</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Person04</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">1.38</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Person05</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">0.89</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Person06</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">0.58</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Person07</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">0.33</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Person08</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">1.93</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Person09</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">0.39</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Person10</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">2.53</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>************</TD><TD>************</TD><TD>************</TD><TD>************</TD><TD>************</TD><TD>************</TD><TD>************</TD><TD>*******</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B3</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,2),"X","")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,2),"X","")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D3</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,2),"X","")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E3</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,2),"X","")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F3</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,2),"X","")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G3</TH><TD style="TEXT-ALIGN: left">=CHOOSE(RANDBETWEEN(1,2),"X","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H3</TH><TD style="TEXT-ALIGN: left">{=SUM(IF(B3:G3="X",$B$1:$G$1,0))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>


Markmzz

 

[riley76]

Thank you Markmzz for your help you are outstanding.

Thread Closed!

 

[markmzz]

Thank you Markmzz for your help you are outstanding.

Thread Closed!

Thanks for the feedback.

Markmzz