Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically Named Range and IsError()
Greetings! Thanks for any help! Before starting, I don't necessarily need a
treatment, I'm really hoping for a diagnosis. I have a range defined as the following: =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) It expands each time a payment is entered and column E is the amount field. Everything is fine to this point. On a summary page there is a cell totalling the payments, which my application checks. The formula is: =IF(ISERROR(rAmount),0,SUM(rAmount)) Everything is still fine... Unless the user enters exactly 2 or 3 payments throughout the day. One payment works, four payments and up works, but two or three payments causes the named range to return an error. Does anyone have any idea what might cause this? For the record, no error is returned if the formula is changed to =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) Any help is appreciated. Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically Named Range and IsError()
What is rAmount referring to?
--JP On Jan 21, 6:57*am, Stephen Lloyd wrote: Greetings! *Thanks for any help! *Before starting, I don't necessarily need a treatment, I'm really hoping for a diagnosis. I have a range defined as the following: =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) It expands each time a payment is entered and column E is the amount field. * Everything is fine to this point. On a summary page there is a cell totalling the payments, which my application checks. *The formula is: =IF(ISERROR(rAmount),0,SUM(rAmount)) Everything is still fine... Unless the user enters exactly 2 or 3 payments throughout the day. *One payment works, four payments and up works, but two or three payments causes the named range to return an error. Does anyone have any idea what might cause this? *For the record, no error is returned if the formula is changed to =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) Any help is appreciated. *Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically Named Range and IsError()
sorry, rAmount is defined as the dynamic named range
=OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) Column E is the amount field. Amounts are generally in the 100's or thousands. I checked and all the entries are fine (valid numbers). If I artificially add or take out entries so that there is either only one entry or four or more entries the conditional formula =IF(ISERROR(rAmount),0,SUM(rAmount)) works great. But when there are either two or three entries it is broken and returns 0. I appreciate the followup question. "JP" wrote: What is rAmount referring to? --JP On Jan 21, 6:57 am, Stephen Lloyd wrote: Greetings! Thanks for any help! Before starting, I don't necessarily need a treatment, I'm really hoping for a diagnosis. I have a range defined as the following: =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) It expands each time a payment is entered and column E is the amount field. Everything is fine to this point. On a summary page there is a cell totalling the payments, which my application checks. The formula is: =IF(ISERROR(rAmount),0,SUM(rAmount)) Everything is still fine... Unless the user enters exactly 2 or 3 payments throughout the day. One payment works, four payments and up works, but two or three payments causes the named range to return an error. Does anyone have any idea what might cause this? For the record, no error is returned if the formula is changed to =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) Any help is appreciated. Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically Named Range and IsError()
=IF(ISERROR(rAmount),0,SUM(rAmount))
rAmount is a range of cells therefore the formula would have to be array entered. Also, it depends on where the formula is entered as to how it "acts". There is a rule called the implicit intersection which applies to array formulas. If an array formula is not entered as an array it will only evaluate references which are on the same row/column that the formula is entered on. However, that formula as written is not logically correct. You'd want to use this formula array entered** : =IF(OR(ISERROR(rAmount)),0,SUM(rAmount)) ** 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. =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) That formula is using the SUM function so that eliminates the need to array enter. Another way that will ignore any errors: =SUMIF(rAmount,"<1E100") -- Biff Microsoft Excel MVP "Stephen Lloyd" wrote in message ... sorry, rAmount is defined as the dynamic named range =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) Column E is the amount field. Amounts are generally in the 100's or thousands. I checked and all the entries are fine (valid numbers). If I artificially add or take out entries so that there is either only one entry or four or more entries the conditional formula =IF(ISERROR(rAmount),0,SUM(rAmount)) works great. But when there are either two or three entries it is broken and returns 0. I appreciate the followup question. "JP" wrote: What is rAmount referring to? --JP On Jan 21, 6:57 am, Stephen Lloyd wrote: Greetings! Thanks for any help! Before starting, I don't necessarily need a treatment, I'm really hoping for a diagnosis. I have a range defined as the following: =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) It expands each time a payment is entered and column E is the amount field. Everything is fine to this point. On a summary page there is a cell totalling the payments, which my application checks. The formula is: =IF(ISERROR(rAmount),0,SUM(rAmount)) Everything is still fine... Unless the user enters exactly 2 or 3 payments throughout the day. One payment works, four payments and up works, but two or three payments causes the named range to return an error. Does anyone have any idea what might cause this? For the record, no error is returned if the formula is changed to =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) Any help is appreciated. Thanks in advance! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically Named Range and IsError()
Biff,
Thanks for your reply. Ok, I'll buy that. Especially since, my total (=IF(Iserror(.....)) is on row 5 of worksheet 1 and it works when the records reach row 5 of worksheet two. However, it still works when there is only one record. Is this because the range is only a single cell and not an array? On Jan 21, 12:07*pm, "T. Valko" wrote: =IF(ISERROR(rAmount),0,SUM(rAmount)) rAmount is a range of cells therefore the formula would have to be array entered. Also, it depends on where the formula is entered as to how it "acts". There is a rule called the implicit intersection which applies to array formulas. If an array formula is not entered as an array it will only evaluate references which are on the same row/column that the formula is entered on. However, that formula as written is not logically correct. You'd want to use this formula array entered** : =IF(OR(ISERROR(rAmount)),0,SUM(rAmount)) ** 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. =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) That formula is using the SUM function so that eliminates the need to array enter. Another way that will ignore any errors: =SUMIF(rAmount,"<1E100") -- Biff Microsoft Excel MVP "Stephen Lloyd" wrote in message ... sorry, rAmount is defined as the dynamic named range =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) Column E is the amount field. *Amounts are generally in the 100's or thousands. *I checked and all the entries are fine (valid numbers). *If I artificially add or take out entries so that there is either only one entry or four or more entries the conditional formula =IF(ISERROR(rAmount),0,SUM(rAmount)) works great. *But when there are either two or three entries it is broken and returns 0. I appreciate the followup question. "JP" wrote: What is rAmount referring to? --JP On Jan 21, 6:57 am, Stephen Lloyd wrote: Greetings! *Thanks for any help! *Before starting, I don't necessarily need a treatment, I'm really hoping for a diagnosis. I have a range defined as the following: =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) It expands each time a payment is entered and column E is the amount field. Everything is fine to this point. On a summary page there is a cell totalling the payments, which my application checks. *The formula is: =IF(ISERROR(rAmount),0,SUM(rAmount)) Everything is still fine... Unless the user enters exactly 2 or 3 payments throughout the day. *One payment works, four payments and up works, but two or three payments causes the named range to return an error. Does anyone have any idea what might cause this? *For the record, no error is returned if the formula is changed to =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) Any help is appreciated. *Thanks in advance!- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamically Named Range and IsError()
When the dynamic range rAmount is just a single and if that single cell is
the first cell of the referenced range then the non-array entered array formula would work. -- Biff Microsoft Excel MVP "Aviashn" wrote in message ... Biff, Thanks for your reply. Ok, I'll buy that. Especially since, my total (=IF(Iserror(.....)) is on row 5 of worksheet 1 and it works when the records reach row 5 of worksheet two. However, it still works when there is only one record. Is this because the range is only a single cell and not an array? On Jan 21, 12:07 pm, "T. Valko" wrote: =IF(ISERROR(rAmount),0,SUM(rAmount)) rAmount is a range of cells therefore the formula would have to be array entered. Also, it depends on where the formula is entered as to how it "acts". There is a rule called the implicit intersection which applies to array formulas. If an array formula is not entered as an array it will only evaluate references which are on the same row/column that the formula is entered on. However, that formula as written is not logically correct. You'd want to use this formula array entered** : =IF(OR(ISERROR(rAmount)),0,SUM(rAmount)) ** 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. =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) That formula is using the SUM function so that eliminates the need to array enter. Another way that will ignore any errors: =SUMIF(rAmount,"<1E100") -- Biff Microsoft Excel MVP "Stephen Lloyd" wrote in message ... sorry, rAmount is defined as the dynamic named range =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) Column E is the amount field. Amounts are generally in the 100's or thousands. I checked and all the entries are fine (valid numbers). If I artificially add or take out entries so that there is either only one entry or four or more entries the conditional formula =IF(ISERROR(rAmount),0,SUM(rAmount)) works great. But when there are either two or three entries it is broken and returns 0. I appreciate the followup question. "JP" wrote: What is rAmount referring to? --JP On Jan 21, 6:57 am, Stephen Lloyd wrote: Greetings! Thanks for any help! Before starting, I don't necessarily need a treatment, I'm really hoping for a diagnosis. I have a range defined as the following: =OFFSET(Entries!$E$2,0,0,COUNTA(Entries!$E:$E)-1,1) It expands each time a payment is entered and column E is the amount field. Everything is fine to this point. On a summary page there is a cell totalling the payments, which my application checks. The formula is: =IF(ISERROR(rAmount),0,SUM(rAmount)) Everything is still fine... Unless the user enters exactly 2 or 3 payments throughout the day. One payment works, four payments and up works, but two or three payments causes the named range to return an error. Does anyone have any idea what might cause this? For the record, no error is returned if the formula is changed to =IF(ISERROR(SUM(rAmount)),0,SUM(rAmount)) Any help is appreciated. Thanks in advance!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Include sheetname dynamically in named range? | Excel Discussion (Misc queries) | |||
Dynamically Changing Named Ranges | Excel Worksheet Functions | |||
Add a data series dynamically to a named range? | Charts and Charting in Excel | |||
dynamically building references to named ranges | Excel Discussion (Misc queries) | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions |