Giving a letter a numerical value

[imnotgoodatexcel]

I'm working on a spreadsheet and I want the letter "E" to have a numerical value of zero, and not necessarily in just place as my sheet will be regularly updated. If you're a fan of golf, you will understand what I am doing. I'm basically making a custom leader board for a fantasy golf game that I'm doing, and I'd like for the letter E to represent the value of zero when I am adding up the scores.

For example
A1: -1
A2: -3
A3: 4
A4: E
A5: -2
A6: 3
A7: 3
A8: -5
Total: -11 (have a formula set up to only count the best 4 scores).

I want to be able to add up those columns, and since only the lowest 4 scores count, I need E to equal zero. I get that I could just put 0 there, so I'm being a little picky (in golf E represents Even par, essentially a value of zero for the purposes of this game). There are 20+ teams in the game so I need E to be recognized as zero in each of those fields.

Hopefully what I'm looking for is clear, and thanks in advance for the help.
Also, I have spreadsheet on google docs. Dont know if that will make a difference.

[MarvinP]

Hi imnotgoodatexcel and welcome to the forum,

I can tell you are an independent kind of person. The problem is that we have standards where we add numbers to get totals and not letters. My question is, if you use Even for Par then why not use Par, Birdie and Bogey for your scores? If you then used the full word you would still not be able to add the letters or words.

My suggestion is to use E (if you must) and then replace all of them with zero before doing your calculations.

[imnotgoodatexcel]

Hi imnotgoodatexcel and welcome to the forum,

I can tell you are an independent kind of person. The problem is that we have standards where we add numbers to get totals and not letters. My question is, if you use Even for Par then why not use Par, Birdie and Bogey for your scores? If you then used the full word you would still not be able to add the letters or words.

My suggestion is to use E (if you must) and then replace all of them with zero before doing your calculations.

I'm not making a hole by hole scorecard, but each golfers individual score to par for the entire tournament. Does that make sense? Like if you're looking at a leader board of a golf tournament, you see the players name and score in relation to even par. Like this.

Woods: -5
Scott: -3
Mickelson: E
Smith: 1
Jones: 2
Rose: 5
Johnson: 3

And yes I get that I could put 0 instead of E and avoid any problems. But since that just looks weird and you don't see that normally on a golf leader board, I was wondering if there was a way to give the letter E numerical value throughout for the auto sum purpose. And I have auto sum set up because there are going to be 20 or so players in this, each with a team of 8, and the real life leader boards will be constantly changing and I'd like to be able to update it somewhat live.

[shg]

Format the numbers as 0;-0;\E

[imnotgoodatexcel]

Format the numbers as 0;-0;\E

I don't know where to do that or what you mean exactly.

And would that automatically make E equal to zero everywhere on the spreadsheet?

[MarvinP]

OK - I have an answer for you. It is in Custom Number Formats.

When you put a zero in for a number it will display "Even". Excel does everything. I'm amazed it will do what you want.

Select a range of cells and look at their custom number format. See the attached. Put numbers in for the two columns I have defined custom number formats and then look at what I've done.

Look at http://www.databison.com/custom-form...bers-and-text/

[martindwilson]

Or
=sum(small(index(substitute(a1:a8,"e",0)+0,0),{1,2,3,4}))

[Tony Valko]

Or
=sum(small(index(substitute(a1:a8,"e",0)+0,0),{1,2,3,4}))

SUBSTITUTE is case sensitive.

[Tony Valko]

Another possibility.

Assuming no empty cells within the range.

Array entered**:

=SUM(SMALL(IF(ISNUMBER(A1:A8),A1:A8,0),{1,2,3,4}))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[imnotgoodatexcel]

OK - I have an answer for you. It is in Custom Number Formats.

When you put a zero in for a number it will display "Even". Excel does everything. I'm amazed it will do what you want.

Select a range of cells and look at their custom number format. See the attached. Put numbers in for the two columns I have defined custom number formats and then look at what I've done.

Look at http://www.databison.com/custom-form...bers-and-text/

Thanks so much for your help. This does work, but I don't see this feature on google spreadsheet. So unfortunately I can't use it.

[imnotgoodatexcel]

Or
=sum(small(index(substitute(a1:a8,"e",0)+0,0),{1,2,3,4}))

I dont understand the code at all or why it works, all I know is that it does work. Thanks!!!!!

[martindwilson]

it was an uppercase e when i posted it! infact all of the formula was,wonder why it gets rendered in lower case

[imnotgoodatexcel]

Or
=sum(small(index(substitute(a1:a8,"e",0)+0,0),{1,2,3,4}))

Is there anyway to make the summed total say "E" if the summed value is 0?

[shg]

it was an uppercase e when i posted it! infact all of the formula was,wonder why it gets rendered in lower case

Anti-shout; it the entire post is uppercase, it gets converted to lower.

[martindwilson]

=iferror(1/(1/sum(small(index(substitute(a1:a8,"E",0)+0,0),{1,2,3,4}))),"E")
or
=if(sum(small(index(substitute(a1:a8,"E",0)+0,0),{1,2,3,4}))=0,"e",sum(small(index(substitute(a1:a8,"E",0)+0,0),{1,2,3,4})))

[Tony Valko]

Try this array formula**

=IFERROR(1/(1/SUM(SMALL(IF(ISNUMBER(A1:A8),A1:A8,0),{1,2,3,4}))),"E")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

EDIT: I think I misunderstood about the sum =0. The edited formula should do what you wanted.

EDIT 2: Alright, this is my final answer! I was overlooking the fact that there could possibly be all letter "E" scores!

[imnotgoodatexcel]

Try this array formula**

=IFERROR(1/(1/SUM(SMALL(IF(ISNUMBER(A1:A8),A1:A8,0),{1,2,3,4}))),"E")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

EDIT: I think I misunderstood about the sum =0. The edited formula should do what you wanted.

EDIT 2: Alright, this is my final answer! I was overlooking the fact that there could possibly be all letter "E" scores!

Try this array formula**

=IFERROR(1/(1/SUM(SMALL(IF(ISNUMBER(A1:A8),A1:A8,0),{1,2,3,4}))),"E")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

EDIT: I think I misunderstood about the sum =0. The edited formula should do what you wanted.

EDIT 2: Alright, this is my final answer! I was overlooking the fact that there could possibly be all letter "E" scores!

This works but it gives me another issue. I was taking the summed value and putting it into a formula for an automated standings chart. Now that the summed value is E it doesn't recognized it as zero and it doesn't work.

Heres my standings formula: (i have a box set up from D47:E73 with player names in D and scores in E)
Column A
=INDEX($D$47:$D$73,SMALL(IF($E47:$E$73=$B10,ROW($D$47:$D$73)-MIN(ROW($D$47:$D$73))+1),COUNTIF($B$10:$B10,$B10)))
Column B
=SMALL($E$47:$E$73,ROW(D1))

This works fine if the total shows 0, but when it shows E it gets screwed up. I set up an extra box to substitute E for 0 in another chart and moved the range of the leader board to that and it didnt work either.
There might not be a way to make this work, which I'm ok with. Thanks in advance for any help.

[imnotgoodatexcel]

Screen Shot 2014-04-08 at 3.22.07 PM.png

Heres a screen shot of the spreadsheet to help make sense of it hopefully

[Tony Valko]

Maybe you can use shg's suggestion in post #4 to just use numeric 0 but custom format it to display as E?

If that won't work then I'd need to see the file to get a better idea of what you're trying to do and how your data is structured.

[imnotgoodatexcel]

I figured it out by making a bunch of different boxes and using IF. It looks sloppy but I can just white it all out at the bottom of the spreadsheet. Thanks again for all you help.

I wasn't able to use the custom format because I'm using google spreadsheet and it doesn't have that feature.

[Tony Valko]

Good deal. Thanks for the feedback!