| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct value error
Another Excel user states that this equation works on his computer
=SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct value error
Did you enter the formula as an array?
The IF portion of the formula makes it an array. Enter it using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Biff "Brad" wrote in message ... Another Excel user states that this equation works on his computer =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct value error
It does, but it is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Brad" wrote in message ... Another Excel user states that this equation works on his computer =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct value error
Was it entered with Ctrl+Shift+Enter (array formula)? If not, try entering
with Ctrl+Shift+Enter "Brad" wrote: Another Excel user states that this equation works on his computer =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct value error
Thank you
"Toppers" wrote: Was it entered with Ctrl+Shift+Enter (array formula)? If not, try entering with Ctrl+Shift+Enter "Brad" wrote: Another Excel user states that this equation works on his computer =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct value error
Thank you
"Biff" wrote: Did you enter the formula as an array? The IF portion of the formula makes it an array. Enter it using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Biff "Brad" wrote in message ... Another Excel user states that this equation works on his computer =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct value error
Thank you
"Bob Phillips" wrote: It does, but it is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Brad" wrote in message ... Another Excel user states that this equation works on his computer =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct value error
You're welcome!
Biff "Brad" wrote in message ... Thank you "Biff" wrote: Did you enter the formula as an array? The IF portion of the formula makes it an array. Enter it using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Biff "Brad" wrote in message ... Another Excel user states that this equation works on his computer =SUMPRODUCT(IF(C10:C21<1099.41-F10:F21,C10:C21,1099.41-F10:F21),M10:M21) However, when I formula audit - it gives me a value error on the first c10:c20. I know that all my values are good. If I remove the "IF" I can get an answer - again just confirming that it is not a problem with the data. I'm using v. 2003 Any ideas |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT formula returning #VALUE! error | Excel Worksheet Functions | |||
| Error Handler Not Working | Excel Discussion (Misc queries) | |||
| DIV/0! error in SumProduct formula with no division | Excel Worksheet Functions | |||
| Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
| #REF error | Excel Worksheet Functions | |||
Linear Mode
