Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
I have a worksheet where I need to calculate the sum of a range of cells
containing a text string and a number, for example: If I had the cell values: 8ABC, 10ABC, 2XYZ And I wanted to calculate the sum of the numeric portions of cells containing the strong ABC, the result would be 18. I know the text string will always be 3 characters long, and so I've tried this: =SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3))) Which, I think, should check all cells in the range L49 to CY49 to see if the rightmost 3 characters are"ABC" and, where that's true, assign the numeric value of that cell by trimming the text string (so 10ABC becomes 10 and so on) and then adding those values together. But, the formula dies on the first step, returning #value! for the function RIGHT($L49:CY49). If I click the Insert Function button, the function appears to evaluate correctly in the "Function arguments" dialogue box. I've seen a similar function work in another spreadsheet, but the function is surrounded by curly braces in the function bar. I don't know if that makes a difference. Any ideas what I'm doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
Simon Woods wrote:
I have a worksheet where I need to calculate the sum of a range of cells containing a text string and a number, for example: If I had the cell values: 8ABC, 10ABC, 2XYZ And I wanted to calculate the sum of the numeric portions of cells containing the strong ABC, the result would be 18. I know the text string will always be 3 characters long, and so I've tried this: =SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3))) Which, I think, should check all cells in the range L49 to CY49 to see if the rightmost 3 characters are"ABC" and, where that's true, assign the numeric value of that cell by trimming the text string (so 10ABC becomes 10 and so on) and then adding those values together. But, the formula dies on the first step, returning #value! for the function RIGHT($L49:CY49). If I click the Insert Function button, the function appears to evaluate correctly in the "Function arguments" dialogue box. I've seen a similar function work in another spreadsheet, but the function is surrounded by curly braces in the function bar. I don't know if that makes a difference. Any ideas what I'm doing wrong? Lookup "About array formulas and array constants" in the help file. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
Hi
With no blank cell in the range try =SUMPRODUCT((RIGHT(L49:CY49,3)="ABC")*(LEFT(L49:CY 49,LEN(L49:CY49)-3))) Mike "Simon Woods" wrote: I have a worksheet where I need to calculate the sum of a range of cells containing a text string and a number, for example: If I had the cell values: 8ABC, 10ABC, 2XYZ And I wanted to calculate the sum of the numeric portions of cells containing the strong ABC, the result would be 18. I know the text string will always be 3 characters long, and so I've tried this: =SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3))) Which, I think, should check all cells in the range L49 to CY49 to see if the rightmost 3 characters are"ABC" and, where that's true, assign the numeric value of that cell by trimming the text string (so 10ABC becomes 10 and so on) and then adding those values together. But, the formula dies on the first step, returning #value! for the function RIGHT($L49:CY49). If I click the Insert Function button, the function appears to evaluate correctly in the "Function arguments" dialogue box. I've seen a similar function work in another spreadsheet, but the function is surrounded by curly braces in the function bar. I don't know if that makes a difference. Any ideas what I'm doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
On reflection this is batter
=SUM(IF(RIGHT(L49:CY49,3)="abc",LEFT(L49:CY49,LEN( L49:CY49)-3)*1)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Mike H" wrote: Hi With no blank cell in the range try =SUMPRODUCT((RIGHT(L49:CY49,3)="ABC")*(LEFT(L49:CY 49,LEN(L49:CY49)-3))) Mike "Simon Woods" wrote: I have a worksheet where I need to calculate the sum of a range of cells containing a text string and a number, for example: If I had the cell values: 8ABC, 10ABC, 2XYZ And I wanted to calculate the sum of the numeric portions of cells containing the strong ABC, the result would be 18. I know the text string will always be 3 characters long, and so I've tried this: =SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3))) Which, I think, should check all cells in the range L49 to CY49 to see if the rightmost 3 characters are"ABC" and, where that's true, assign the numeric value of that cell by trimming the text string (so 10ABC becomes 10 and so on) and then adding those values together. But, the formula dies on the first step, returning #value! for the function RIGHT($L49:CY49). If I click the Insert Function button, the function appears to evaluate correctly in the "Function arguments" dialogue box. I've seen a similar function work in another spreadsheet, but the function is surrounded by curly braces in the function bar. I don't know if that makes a difference. Any ideas what I'm doing wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
Cheers Glenn, that sorted out the #value! problem. Still isn't working yet
though, just returns 0 instead. "Glenn" wrote: Lookup "About array formulas and array constants" in the help file. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
That works perfectly, thank you.
One question, though. What does the *1 do at the end of the LEN() statement that means it works, but the same statement without it does not? Not critical, I'm just curious "Mike H" wrote: On reflection this is batter =SUM(IF(RIGHT(L49:CY49,3)="abc",LEFT(L49:CY49,LEN( L49:CY49)-3)*1)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Mike H" wrote: Hi With no blank cell in the range try =SUMPRODUCT((RIGHT(L49:CY49,3)="ABC")*(LEFT(L49:CY 49,LEN(L49:CY49)-3))) Mike "Simon Woods" wrote: I have a worksheet where I need to calculate the sum of a range of cells containing a text string and a number, for example: If I had the cell values: 8ABC, 10ABC, 2XYZ And I wanted to calculate the sum of the numeric portions of cells containing the strong ABC, the result would be 18. I know the text string will always be 3 characters long, and so I've tried this: =SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3))) Which, I think, should check all cells in the range L49 to CY49 to see if the rightmost 3 characters are"ABC" and, where that's true, assign the numeric value of that cell by trimming the text string (so 10ABC becomes 10 and so on) and then adding those values together. But, the formula dies on the first step, returning #value! for the function RIGHT($L49:CY49). If I click the Insert Function button, the function appears to evaluate correctly in the "Function arguments" dialogue box. I've seen a similar function work in another spreadsheet, but the function is surrounded by curly braces in the function bar. I don't know if that makes a difference. Any ideas what I'm doing wrong? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
Hi,
the string being evaluated is text so the formula returns text so multiplying the text number ("22") *1 turns it into a real number (22). Mike "Simon Woods" wrote: That works perfectly, thank you. One question, though. What does the *1 do at the end of the LEN() statement that means it works, but the same statement without it does not? Not critical, I'm just curious "Mike H" wrote: On reflection this is batter =SUM(IF(RIGHT(L49:CY49,3)="abc",LEFT(L49:CY49,LEN( L49:CY49)-3)*1)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Mike H" wrote: Hi With no blank cell in the range try =SUMPRODUCT((RIGHT(L49:CY49,3)="ABC")*(LEFT(L49:CY 49,LEN(L49:CY49)-3))) Mike "Simon Woods" wrote: I have a worksheet where I need to calculate the sum of a range of cells containing a text string and a number, for example: If I had the cell values: 8ABC, 10ABC, 2XYZ And I wanted to calculate the sum of the numeric portions of cells containing the strong ABC, the result would be 18. I know the text string will always be 3 characters long, and so I've tried this: =SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3))) Which, I think, should check all cells in the range L49 to CY49 to see if the rightmost 3 characters are"ABC" and, where that's true, assign the numeric value of that cell by trimming the text string (so 10ABC becomes 10 and so on) and then adding those values together. But, the formula dies on the first step, returning #value! for the function RIGHT($L49:CY49). If I click the Insert Function button, the function appears to evaluate correctly in the "Function arguments" dialogue box. I've seen a similar function work in another spreadsheet, but the function is surrounded by curly braces in the function bar. I don't know if that makes a difference. Any ideas what I'm doing wrong? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#Value! error when evaluating data in a cell range
Apologies,
i forgot to mention thanks for the feed back and how refreshing it is to have a poster enquire as to how their problem is solved. Mike "Mike H" wrote: Hi, the string being evaluated is text so the formula returns text so multiplying the text number ("22") *1 turns it into a real number (22). Mike "Simon Woods" wrote: That works perfectly, thank you. One question, though. What does the *1 do at the end of the LEN() statement that means it works, but the same statement without it does not? Not critical, I'm just curious "Mike H" wrote: On reflection this is batter =SUM(IF(RIGHT(L49:CY49,3)="abc",LEFT(L49:CY49,LEN( L49:CY49)-3)*1)) 'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Mike H" wrote: Hi With no blank cell in the range try =SUMPRODUCT((RIGHT(L49:CY49,3)="ABC")*(LEFT(L49:CY 49,LEN(L49:CY49)-3))) Mike "Simon Woods" wrote: I have a worksheet where I need to calculate the sum of a range of cells containing a text string and a number, for example: If I had the cell values: 8ABC, 10ABC, 2XYZ And I wanted to calculate the sum of the numeric portions of cells containing the strong ABC, the result would be 18. I know the text string will always be 3 characters long, and so I've tried this: =SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49, LEN($L49:$CY49)-3))) Which, I think, should check all cells in the range L49 to CY49 to see if the rightmost 3 characters are"ABC" and, where that's true, assign the numeric value of that cell by trimming the text string (so 10ABC becomes 10 and so on) and then adding those values together. But, the formula dies on the first step, returning #value! for the function RIGHT($L49:CY49). If I click the Insert Function button, the function appears to evaluate correctly in the "Function arguments" dialogue box. I've seen a similar function work in another spreadsheet, but the function is surrounded by curly braces in the function bar. I don't know if that makes a difference. Any ideas what I'm doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro error, range object need data? | Charts and Charting in Excel | |||
Named range as chart data reference (error) | Charts and Charting in Excel | |||
Evaluating a range | Excel Discussion (Misc queries) | |||
How to have an error popup once range cell amount is greater than | Excel Discussion (Misc queries) | |||
evaluating text cell contents | Excel Discussion (Misc queries) |