Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CSE function with #VALUE errors
Hi,
In a column of a spreadsheet I use at work is data for the number of transactions of different types, in a precise format (2 buy, 3 sell, 5 sell, etc). I have to total the transactions of different types, and I thought I could get excel to do it for me. More than that, I thought a CSE could do it in one cell, but the function seems to have become a bit of a monster. This is my tester for it; =SUM(IF(EXACT(RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3)),"buy"), VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1)),0)) it sums only the numbers in cells that end in "buy". It works a charm, but it can't handle any blank cells in the range, which makes it all but useless for my purposes. I tried experimenting with ISERROR, but without much luck. This is my best attempt so far; =SUM(IF(EXACT(IF(ISERROR(FIND(" ",A1:A3)),0, RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3))),"buy"), IF(ISERROR(FIND(" ",A1:A3)),0,VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1))),0)) Now, if the first cell in the range is empty it still gives #VALUE, but any other blank cell just ruins the sum and it gives 0. That's the best I can do. I realise now that maybe solving it inline is probably not the best way to do it, and that I'm betraying all the laws of readability, but I'm determined to make it work now, and I'd really like some help. Am I barking up the wrong tree with ISERROR? Is there a better function you know of that can help? What do you think? thanks P |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CSE function with #VALUE errors
Try this array formula (CSE):
All on one line. =SUM(IF(ISNUMBER(SEARCH("buy",A1:A10)), --LEFT(A1:A10,FIND(" ",A1:A10)-1))) -- Biff Microsoft Excel MVP "Phillip" wrote in message ... Hi, In a column of a spreadsheet I use at work is data for the number of transactions of different types, in a precise format (2 buy, 3 sell, 5 sell, etc). I have to total the transactions of different types, and I thought I could get excel to do it for me. More than that, I thought a CSE could do it in one cell, but the function seems to have become a bit of a monster. This is my tester for it; =SUM(IF(EXACT(RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3)),"buy"), VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1)),0)) it sums only the numbers in cells that end in "buy". It works a charm, but it can't handle any blank cells in the range, which makes it all but useless for my purposes. I tried experimenting with ISERROR, but without much luck. This is my best attempt so far; =SUM(IF(EXACT(IF(ISERROR(FIND(" ",A1:A3)),0, RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3))),"buy"), IF(ISERROR(FIND(" ",A1:A3)),0,VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1))),0)) Now, if the first cell in the range is empty it still gives #VALUE, but any other blank cell just ruins the sum and it gives 0. That's the best I can do. I realise now that maybe solving it inline is probably not the best way to do it, and that I'm betraying all the laws of readability, but I'm determined to make it work now, and I'd really like some help. Am I barking up the wrong tree with ISERROR? Is there a better function you know of that can help? What do you think? thanks P |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CSE function with #VALUE errors
On Thu, 04 Oct 2007 18:12:46 -0400, T. Valko wrote:
Try this array formula (CSE): All on one line. =SUM(IF(ISNUMBER(SEARCH("buy",A1:A10)), --LEFT(A1:A10,FIND(" ",A1:A10)-1))) Thanks. That'll make life a lot easier P |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CSE function with #VALUE errors
"Phillip" wrote in message
... On Thu, 04 Oct 2007 18:12:46 -0400, T. Valko wrote: Try this array formula (CSE): All on one line. =SUM(IF(ISNUMBER(SEARCH("buy",A1:A10)), --LEFT(A1:A10,FIND(" ",A1:A10)-1))) Thanks. That'll make life a lot easier P You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value errors in Match function | Excel Worksheet Functions | |||
What is the function to calculate standard errors of the mean? | Excel Worksheet Functions | |||
help with errors with Sum function | Excel Discussion (Misc queries) | |||
Errors using a new function | Excel Discussion (Misc queries) | |||
if function errors | Excel Worksheet Functions |