Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and format issues
I am currently using the formula
=SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) Column J is employee Id and could be formated as text or number ( raw file) Column AS is formated as a number Cell T4007 is formated as a number The problem I have I get an #VALUE! unless I remove the -- in the formula above and enter the value in cell t4007 as text Any advice? ce |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and format issues
Hi Curtis
With a small set of sample data entered in A1:B5 with Numeric 1 5 1 -6 1 -7 Text 1 8 1 -3 =SUMPRODUCT((--A1:A5=C1)*(B1:B5<0)*(B1:B5)) correctly returns -16 with a numeric 1 in cell C1, as all values in column A have been coerced to numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -13 with a numeric 1 in cell C1 because the text 1's have not been coerced to Numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -3 with a Text 1 entered in C1 Each of these is correct, in terms of what formula and data are used. It is only the first one that returns the correct value in the context of what you are wanting. If you are getting #Value errors, there must be further problems with your data. I would not trust the result you are obtaining without the double unary minuses and the text entry in T4007, albeit it is not returning you an error. -- Regards Roger Govier "Curtis" wrote in message ... I am currently using the formula =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) Column J is employee Id and could be formated as text or number ( raw file) Column AS is formated as a number Cell T4007 is formated as a number The problem I have I get an #VALUE! unless I remove the -- in the formula above and enter the value in cell t4007 as text Any advice? ce |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and format issues
Then I am Stumped.
Using =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$40000)*($AS$5:$A S$4000)) When cell $t$4007 is text or not returns gives me a value 0.00 when text and #value! when formated as general I am not sure if it is worth mentioning but thee number typed in cell $t$4007 refers to that value in column J ce "Roger Govier" wrote: Hi Curtis With a small set of sample data entered in A1:B5 with Numeric 1 5 1 -6 1 -7 Text 1 8 1 -3 =SUMPRODUCT((--A1:A5=C1)*(B1:B5<0)*(B1:B5)) correctly returns -16 with a numeric 1 in cell C1, as all values in column A have been coerced to numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -13 with a numeric 1 in cell C1 because the text 1's have not been coerced to Numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -3 with a Text 1 entered in C1 Each of these is correct, in terms of what formula and data are used. It is only the first one that returns the correct value in the context of what you are wanting. If you are getting #Value errors, there must be further problems with your data. I would not trust the result you are obtaining without the double unary minuses and the text entry in T4007, albeit it is not returning you an error. -- Regards Roger Govier "Curtis" wrote in message ... I am currently using the formula =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) Column J is employee Id and could be formated as text or number ( raw file) Column AS is formated as a number Cell T4007 is formated as a number The problem I have I get an #VALUE! unless I remove the -- in the formula above and enter the value in cell t4007 as text Any advice? ce |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and format issues
Hi Curtis
Send me a copy of the file and I will see if I can see what the problem is. To send direct, remove NOSPAM from my address -- Regards Roger Govier "Curtis" wrote in message ... Then I am Stumped. Using =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$40000)*($AS$5:$A S$4000)) When cell $t$4007 is text or not returns gives me a value 0.00 when text and #value! when formated as general I am not sure if it is worth mentioning but thee number typed in cell $t$4007 refers to that value in column J ce "Roger Govier" wrote: Hi Curtis With a small set of sample data entered in A1:B5 with Numeric 1 5 1 -6 1 -7 Text 1 8 1 -3 =SUMPRODUCT((--A1:A5=C1)*(B1:B5<0)*(B1:B5)) correctly returns -16 with a numeric 1 in cell C1, as all values in column A have been coerced to numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -13 with a numeric 1 in cell C1 because the text 1's have not been coerced to Numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -3 with a Text 1 entered in C1 Each of these is correct, in terms of what formula and data are used. It is only the first one that returns the correct value in the context of what you are wanting. If you are getting #Value errors, there must be further problems with your data. I would not trust the result you are obtaining without the double unary minuses and the text entry in T4007, albeit it is not returning you an error. -- Regards Roger Govier "Curtis" wrote in message ... I am currently using the formula =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) Column J is employee Id and could be formated as text or number ( raw file) Column AS is formated as a number Cell T4007 is formated as a number The problem I have I get an #VALUE! unless I remove the -- in the formula above and enter the value in cell t4007 as text Any advice? ce |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct and format issues
Hi Curtis
File received and returned. For the benefit of others, the problem was caused by there being and 10 rows in column J with alpha entries. Once these were changed to numeric, the formula worked fine. Another Sumproduct formula on the sheet was returning #VALUE errors. In this case, based upon values in column E. Column E contained formulae =IF(AV5=4.75,1," ") the fact that it was returning space " " rather than null "" was the problem. As this was a hidden column, I changed it to =IF(AV5=4.75,1,0) rather than using null. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Curtis Send me a copy of the file and I will see if I can see what the problem is. To send direct, remove NOSPAM from my address -- Regards Roger Govier "Curtis" wrote in message ... Then I am Stumped. Using =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$40000)*($AS$5:$A S$4000)) When cell $t$4007 is text or not returns gives me a value 0.00 when text and #value! when formated as general I am not sure if it is worth mentioning but thee number typed in cell $t$4007 refers to that value in column J ce "Roger Govier" wrote: Hi Curtis With a small set of sample data entered in A1:B5 with Numeric 1 5 1 -6 1 -7 Text 1 8 1 -3 =SUMPRODUCT((--A1:A5=C1)*(B1:B5<0)*(B1:B5)) correctly returns -16 with a numeric 1 in cell C1, as all values in column A have been coerced to numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -13 with a numeric 1 in cell C1 because the text 1's have not been coerced to Numeric values. =SUMPRODUCT((A1:A5=C1)*(B1:B5<0)*(B1:B5)) returns -3 with a Text 1 entered in C1 Each of these is correct, in terms of what formula and data are used. It is only the first one that returns the correct value in the context of what you are wanting. If you are getting #Value errors, there must be further problems with your data. I would not trust the result you are obtaining without the double unary minuses and the text entry in T4007, albeit it is not returning you an error. -- Regards Roger Govier "Curtis" wrote in message ... I am currently using the formula =SUMPRODUCT((--$J$5:$J$4000=$T$4007)*($AS$5:$AS$4000<0)*($AS$5:$A S$4000)) Column J is employee Id and could be formated as text or number ( raw file) Column AS is formated as a number Cell T4007 is formated as a number The problem I have I get an #VALUE! unless I remove the -- in the formula above and enter the value in cell t4007 as text Any advice? ce |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Date Format Issues Access to Excel | Excel Discussion (Misc queries) | |||
date format/charting issues | Excel Discussion (Misc queries) | |||
format saving issues | Excel Discussion (Misc queries) | |||
Number Format Issues-Decimal Places | Excel Worksheet Functions |