Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
testing range of cells for part of cell content
Hi, I'm new to advanced excel formulas so please bear with me.
So I'm trying to make a formula that sums only certain values in a range of cells. The problem is I need to manipulate each cell content before testing/adding to the sum (but not do this permanently of course). For example, if i have a range of 3 cells that contain the values "P90", "PHP", "B60", I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. I haven't been able to get something like this to work, even for really simple formulas like counting the number of cells whose right character is numeric or summing the right characters of all cells in a range. Any suggestions? Maybe i'm thinking of this from too much of a programming perspective? Thanks Daniel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
testing range of cells for part of cell content
I need my formula to sum the last two digits
of only the values starting with "P" and that have a number for the last two. Ok Will the entries to be summed *always* be 3 characters long starting with a "P" follwed by a 2 digit number? -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Hi, I'm new to advanced excel formulas so please bear with me. So I'm trying to make a formula that sums only certain values in a range of cells. The problem is I need to manipulate each cell content before testing/adding to the sum (but not do this permanently of course). For example, if i have a range of 3 cells that contain the values "P90", "PHP", "B60", I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. I haven't been able to get something like this to work, even for really simple formulas like counting the number of cells whose right character is numeric or summing the right characters of all cells in a range. Any suggestions? Maybe i'm thinking of this from too much of a programming perspective? Thanks Daniel |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
testing range of cells for part of cell content
Yes, I believe so. They will all be either letter followed by two digits or 3
letters. "T. Valko" wrote: I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. Ok Will the entries to be summed *always* be 3 characters long starting with a "P" follwed by a 2 digit number? -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Hi, I'm new to advanced excel formulas so please bear with me. So I'm trying to make a formula that sums only certain values in a range of cells. The problem is I need to manipulate each cell content before testing/adding to the sum (but not do this permanently of course). For example, if i have a range of 3 cells that contain the values "P90", "PHP", "B60", I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. I haven't been able to get something like this to work, even for really simple formulas like counting the number of cells whose right character is numeric or summing the right characters of all cells in a range. Any suggestions? Maybe i'm thinking of this from too much of a programming perspective? Thanks Daniel . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
testing range of cells for part of cell content
Try this array formula**.
=SUM(IF(LEFT(A1:A10)="P",IF(ISNUMBER(-RIGHT(A1:A10)),--MID(A1:A10,2,2)))) ** 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. -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Yes, I believe so. They will all be either letter followed by two digits or 3 letters. "T. Valko" wrote: I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. Ok Will the entries to be summed *always* be 3 characters long starting with a "P" follwed by a 2 digit number? -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Hi, I'm new to advanced excel formulas so please bear with me. So I'm trying to make a formula that sums only certain values in a range of cells. The problem is I need to manipulate each cell content before testing/adding to the sum (but not do this permanently of course). For example, if i have a range of 3 cells that contain the values "P90", "PHP", "B60", I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. I haven't been able to get something like this to work, even for really simple formulas like counting the number of cells whose right character is numeric or summing the right characters of all cells in a range. Any suggestions? Maybe i'm thinking of this from too much of a programming perspective? Thanks Daniel . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
testing range of cells for part of cell content
ohhh I understand. I wasn't doing the ctrl+shft+enter thing before. that
works perfectly, thanks! what are the hyphens in the formula for? Thanks for the support! Daniel "T. Valko" wrote: Try this array formula**. =SUM(IF(LEFT(A1:A10)="P",IF(ISNUMBER(-RIGHT(A1:A10)),--MID(A1:A10,2,2)))) ** 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. -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Yes, I believe so. They will all be either letter followed by two digits or 3 letters. "T. Valko" wrote: I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. Ok Will the entries to be summed *always* be 3 characters long starting with a "P" follwed by a 2 digit number? -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Hi, I'm new to advanced excel formulas so please bear with me. So I'm trying to make a formula that sums only certain values in a range of cells. The problem is I need to manipulate each cell content before testing/adding to the sum (but not do this permanently of course). For example, if i have a range of 3 cells that contain the values "P90", "PHP", "B60", I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. I haven't been able to get something like this to work, even for really simple formulas like counting the number of cells whose right character is numeric or summing the right characters of all cells in a range. Any suggestions? Maybe i'm thinking of this from too much of a programming perspective? Thanks Daniel . . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
testing range of cells for part of cell content
=SUM(IF(LEFT(A1:A10)="P",IF(ISNUMBER(-RIGHT(A1:A10)),--MID(A1:A10,2,2))))
what are the hyphens in the formula for? We're using the RIGHT function to extract the last character in the cell to see if it is a number: ISNUMBER(-RIGHT(A1:A10)) The RIGHT function returns a TEXT value even if the value "looks" like a number: A1 = P22 RIGHT(A1) returns "2" as a TEXT value. Even though it looks like the number 2 Excel evaluates it as a TEXT string. The hyphen, also called a unary minus, converts the TEXT character "2" to the numeric value -2. We need to do this so that ISNUMBER will evaluate that last character properly. ISNUMBER("2") = FALSE ISNUMBER(-2) = TRUE The same thing is happening with the MID function. It also returns a TEXT value even if it "looks" like a number. However, in this case we need the positive numeric value because we're summing these values so we use a double unary to convert the TEXT string to a numeric number. The first unary converts the string to a negative: A1 = P22 -MID(A1,2,2) = -22 The second converts the negative numeric value back to a positive numeric number: --MID(A1,2,2) = 22 In the case of the ISNUMBER(-RIGHT(A1:A10)), we're only testing for the existence of a number, we're not concerned about the numbers sign so we can save one keystroke (Wow, one whole keystroke!) by using just a single unary minus. exp101 -- Biff Microsoft Excel MVP "Daniel" wrote in message ... ohhh I understand. I wasn't doing the ctrl+shft+enter thing before. that works perfectly, thanks! what are the hyphens in the formula for? Thanks for the support! Daniel "T. Valko" wrote: Try this array formula**. =SUM(IF(LEFT(A1:A10)="P",IF(ISNUMBER(-RIGHT(A1:A10)),--MID(A1:A10,2,2)))) ** 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. -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Yes, I believe so. They will all be either letter followed by two digits or 3 letters. "T. Valko" wrote: I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. Ok Will the entries to be summed *always* be 3 characters long starting with a "P" follwed by a 2 digit number? -- Biff Microsoft Excel MVP "Daniel" wrote in message ... Hi, I'm new to advanced excel formulas so please bear with me. So I'm trying to make a formula that sums only certain values in a range of cells. The problem is I need to manipulate each cell content before testing/adding to the sum (but not do this permanently of course). For example, if i have a range of 3 cells that contain the values "P90", "PHP", "B60", I need my formula to sum the last two digits of only the values starting with "P" and that have a number for the last two. I haven't been able to get something like this to work, even for really simple formulas like counting the number of cells whose right character is numeric or summing the right characters of all cells in a range. Any suggestions? Maybe i'm thinking of this from too much of a programming perspective? Thanks Daniel . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete part of the content on multiple cells in excel? | Excel Discussion (Misc queries) | |||
Can I use the content of a cell as part of filereference in other | Excel Worksheet Functions | |||
display part of the cell content | Excel Discussion (Misc queries) | |||
TESTING A RANGE OF CELLS | New Users to Excel | |||
TESTING A RANGE OF CELLS | Excel Worksheet Functions |