trying to add alpha-numeric text

[keithcuda]

I making a spread sheet for work to track total hours worked on days off.. I am in need of a formula to add only X/16 and X/8 to equal 24, etc. I am trying to keep track of overtime hours for the entire year.

example

8
8
X/16
X/8
8
8
X/4

total hours worked on days off = 28

thanks

 

[Rick Rothstein]

Give this array-entered** formula a try...

=SUM(IF(LEFT(A1:A99)="X",0+MID(A1:A99,3,2)))

**Commit this fomula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: Change the two instances of 99 to row number guaranteed to be larger than the maximum row number you will ever have data in.

 

[keithcuda]

So far that seems to be working!! thank you!
could you explain to me what is going on.

 

[Chris Macro]

You could use this formula beside each of your numbers (this formula targets cell A1) and it will only output the ones that start with an "x". This formula tests to see if the cell starts with an x and if it does it outputs everything after the slash.

=IF(LEFT(A1,1)="x",VALUE(MID(A1,FIND("/",A1)+1,LEN(A1))),0)

Then you could just sum up the numbers.

 

[keithcuda]

ok, What if i have this also, 4/X, 6/X that need to be added...
would it be as simple as changing left to right?

Give this array-entered** formula a try...

=SUM(IF(LEFT(A1:A99)="X",0+MID(A1:A99,3,2)))

**Commit this fomula using CTRL+SHIFT+ENTER and not just Enter by itself



Note: Change the two instances of 99 to row number guaranteed to be larger than the maximum row number you will ever have data in.

 

[Rick Rothstein]

So far that seems to be working!! thank you!
could you explain to me what is going on.

An array formula has an array of values given to it (the range of cells A1:A99 in my example formula) and processes each element in the array of value (each cell in the cells A1:A99) individually... this produces an array of calculated values which, in my formula, are passed to the SUM function to be added up. The way the calculated values are derived is via the IF function.. it looks to see if the first character is an X or not.. if not, FALSE is returned (SUM ignores FALSE when it does its summation because FALSE is not a number) but if it is, the MID function strips away the first two characters and returns whatever follow, but that returned value is a text string which SUM would not add either, but in Excel a number in text format can be changed to a real number by involving it in a mathematical calculation, hence the addition of 0 (which will not physically change the number because adding 0 to a number simply returns that number).

 

[keithcuda]

Never mind, i figured it out... thanks for the help!!

ok, What if i have this also, 4/X, 6/X that need to be added...
would it be as simple as changing left to right?

 

[keithcuda]

I found an error, If I have a cell that contains just an "X" an error occurs. "X" representing a person with a day off that did not work it. Is there any way around this?

Give this array-entered** formula a try...

=SUM(IF(LEFT(A1:A99)="X",0+MID(A1:A99,3,2)))

**Commit this fomula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: Change the two instances of 99 to row number guaranteed to be larger than the maximum row number you will ever have data in.

 

[Rick Rothstein]

I found an error, If I have a cell that contains just an "X" an error occurs. "X" representing a person with a day off that did not work it. Is there any way around this?

Your original message did not indicate that would be a possibility. If it were to be in the format of the other X's (that is, for example, X/8) what would the number after the slash be for this "day off that did not work"... 0, 8, something else? Remember, we here know nothing about your business model, so we have no idea what X means in terms of the sum you want unless you tell us.

 

[keithcuda]

I understand that.. It was a mistake I overlooked when I was trying to explain what I was wanting. I apologize.
If it was a day off that was not worked it would be represented by an "X", X meaning zero hours worked or day off. There will always be an X, X/8, X/16, etc. But an X by itself would equal zero hours.