Question for the excel guru's

[AgustusM]

I am above average on excel, but not a wiz and my wife has a work issue that she wants me to fix for her. I remember doing something of this sort years ago and I don't remember how and haven't been able to word it in a way that Google can find a straight answer. So here is to hoping some human interaction will help.

she needs a formula that will give a numerical value to a list of text options

for example:

A=4
B=3
C=2
D=1
F=0

and then add those values up. (which I am pretty sure I can work that bit out, once I know how to tell excel that a=4 and all that)

anyone know an easy way to do this?

[Draft Dodger]

if it's just a few text options, you can go with an IF formula. there's a limit, though.

[SteveMax58]

Not sure how complex the data your wife is working with happens to be or how many more letters or values she might be dealing with...but here is a fairly straightforward way to do this, based on what is trying to be done.

=SUM(COUNTIF(A1:D1,"A")*4,COUNTIF(A1:D1,"B")*3,COUNTIF(A1:D1,"C")*2,COUNTIF(A1:D1,"D"))

The A1:D1 is the range of cells to enter letters into & then have them summed. Since F=0...I did not include it, but you could certainly add it if you'd like. There may be other ways to handle this, but this was the easiest way I could think of off the bat.

[Flasch186]

is she a teacher? If someone earned 75% of the points on a test and earned a C you cant have a C only be worth 50% of an A. IF you do, you also have to adjust the grading scale that you plug it back into because the new 50% earned will get an F on the traditional scale. I failed a class this way until I appealed it to the dean of the dept.

[RPI-Fan]

is she a teacher? If someone earned 75% of the points on a test and earned a C you cant have a C only be worth 50% of an A. IF you do, you also have to adjust the grading scale that you plug it back into because the new 50% earned will get an F on the traditional scale. I failed a class this way until I appealed it to the dean of the dept.

If you're a professor you can do whatever the fuck you want grading-wise.

[AgustusM]

is she a teacher? If someone earned 75% of the points on a test and earned a C you cant have a C only be worth 50% of an A. IF you do, you also have to adjust the grading scale that you plug it back into because the new 50% earned will get an F on the traditional scale. I failed a class this way until I appealed it to the dean of the dept.

no those aren't even the categories, just something that seemed easy to explain the gist of what I am trying to do.

[AgustusM]

Not sure how complex the data your wife is working with happens to be or how many more letters or values she might be dealing with...but here is a fairly straightforward way to do this, based on what is trying to be done.

=SUM(COUNTIF(A1:D1,"A")*4,COUNTIF(A1:D1,"B")*3,COUNTIF(A1:D1,"C")*2,COUNTIF(A1:D1,"D"))

The A1:D1 is the range of cells to enter letters into & then have them summed. Since F=0...I did not include it, but you could certainly add it if you'd like. There may be other ways to handle this, but this was the easiest way I could think of off the bat.

thanks, I think this might work, trying it now

[AgustusM]

if it's just a few text options, you can go with an IF formula. there's a limit, though.

only 5-6 items I think.

I looked at the IF function, but couldn't figure anything out that made sense in the few seconds I looked at it.

[Pumpy Tudors]

If you're a professor you can do whatever the fuck you want grading-wise.
So that's why my wife makes me wear that fucking tunic and then gives me a D+.

[AgustusM]

Not sure how complex the data your wife is working with happens to be or how many more letters or values she might be dealing with...but here is a fairly straightforward way to do this, based on what is trying to be done.

=SUM(COUNTIF(A1:D1,"A")*4,COUNTIF(A1:D1,"B")*3,COUNTIF(A1:D1,"C")*2,COUNTIF(A1:D1,"D"))

The A1:D1 is the range of cells to enter letters into & then have them summed. Since F=0...I did not include it, but you could certainly add it if you'd like. There may be other ways to handle this, but this was the easiest way I could think of off the bat.

thanks!!! that seemed to work (at least with the small data test)

I am going to try now replacing the A1:D1 with a named range so I can change the range without having to do the formula (I hope)

I really appreciate the response.


PS - still open to other ideas if anyone has anything easier

[red95vette]

You can look at using a vlookup as another option....

For example, here's an actual table I use in one of my spreadsheet.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Rating</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Score</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Average</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Excellent</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Fair</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>-1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Good</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Poor</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>-2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Very Good</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR></TBODY></TABLE>

You use a named range on it and use the Vlookup function (Excel has help on its syntax) and wherever is sees "Fair" for example, it will evaluate to -1 and so on. There is only one caveat, the items in the first column must be in ascending order (for some reason) or Excel sometimes screws up.

[SteveMax58]

You can look at using a vlookup as another option....

For example, here's an actual table I use in one of my spreadsheet.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Rating</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Score</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Average</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Excellent</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Fair</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>-1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Good</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Poor</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>-2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Very Good</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD></TR></TBODY></TABLE>

You use a named range on it and use the Vlookup function (Excel has help on its syntax) and wherever is sees "Fair" for example, it will evaluate to -1 and so on. There is only one caveat, the items in the first column must be in ascending order (for some reason) or Excel sometimes screws up.

Next time add FALSE into your VLOOKUP...then it can be in any order...or more precisely, it will find the first matching value in column 1, starting from the top.

=VLOOKUP("Fair",Rating_Scores,2,FALSE)

[Flasch186]

If you're a professor you can do whatever the fuck you want grading-wise.

Well then, like in my case, if you can prove that the teacher's system, as convoluted as it was, failed most students that earned C's, like me (because a C = 50% of an A and thus he plugs it back into the standard 90-100 = A, 80-89 = B, 70-79 = C, 60-69 = D, 50-59 = F earning all of those students F's), you simply go to the dean with a chart showing it all and get the grade you earned instead of some egotistical simplistic way of trying to grade that morphs your grade 25% less than what it should be, and he gives it to you. I hope that teacher was fired.

[DanGarion]

I know of a Macroguru on the board, but know of no Excelguru on here... :D

[SteveMax58]

thanks!!! that seemed to work (at least with the small data test)

I am going to try now replacing the A1:D1 with a named range so I can change the range without having to do the formula (I hope)

I really appreciate the response.


PS - still open to other ideas if anyone has anything easier

Also...one more thing to note if you dont already know. If your data is totalling on a row or column basis(same data set, multiple entries), you just drag the bottom right corner of the cell in the direction you want, and the formula will adjust accordingly for each cell.

If you, for some reason, do not want the range of cells to change as you drag...then insert a "$" before either the column "$A" or the row"$1", or both"$A$1". This makes the reference range, or portion of the range, an absolute reference, and will not increment as you drag, copy, etc.

So making this whole thing absolute, would look like this:

=SUM(COUNTIF($A$1:$D$1,"A")*4,COUNTIF($A$1:$D$1,"B")*3,COUNTIF($A$1:$D$1,"C")*2,COUNTIF($A$1:$D$1,"D"))

[AgustusM]

Also...one more thing to note if you dont already know. If your data is totalling on a row or column basis(same data set, multiple entries), you just drag the bottom right corner of the cell in the direction you want, and the formula will adjust accordingly for each cell.

If you, for some reason, do not want the range of cells to change as you drag...then insert a "$" before either the column "$A" or the row"$1", or both"$A$1". This makes the reference range, or portion of the range, an absolute reference, and will not increment as you drag, copy, etc.

So making this whole thing absolute, would look like this:

=SUM(COUNTIF($A$1:$D$1,"A")*4,COUNTIF($A$1:$D$1,"B")*3,COUNTIF($A$1:$D$1,"C")*2,COUNTIF($A$1:$D$1,"D"))


thanks, this trick I do know - I like the vlookup and I am going to mess with it, but the COUNTIF worked perfectly for her needs

thanks again to all for the suggestions.