Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
I've created a formula
=SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
Ensure that the range names all define ranges of the same size
"Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
have double checked that and have no luck.
"Duke Carey" wrote: Ensure that the range names all define ranges of the same size "Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
"Harry Seymour" wrote: have double checked that and have no luck. "Duke Carey" wrote: Ensure that the range names all define ranges of the same size "Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
Sounds like there is a text value in the column.
Why not stick to one thread? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Harry Seymour" wrote in message ... I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
You are missing a double quote at the end of a letter B. Should be
(Category="B") "Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
this is now solved.
Another question however, in a similar manner is there a way to find the maximum, minimum and average numbers within the identified fields. e.g. Max.....of (--(Name="Jon"),--(Category=B"),Spend) thanks Harry "Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
Hi Harry
I would just insert 3 rows above your existing header row. Mark your header rowDataFilterAutofilter In row 1 above Spend (let's assume Spend is in column D), enter in D1 =SUBTOTAL(4,D5:D1000) this will give the Maximum in D2 =SUBTOTAL(5,D5:D1000) this will give the Minimum in D3 =SUBTOTAL(1,D5:D1000) this will give the Average Now use the dropdowns to apply a filter to Name and select Jon, and a filter to Category and select B -- Regards Roger Govier "Harry Seymour" wrote in message ... this is now solved. Another question however, in a similar manner is there a way to find the maximum, minimum and average numbers within the identified fields. e.g. Max.....of (--(Name="Jon"),--(Category=B"),Spend) thanks Harry "Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
=MAX(IF((Name="Jon")*(Category=B"),Spend))
etc. which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Harry Seymour" wrote in message ... this is now solved. Another question however, in a similar manner is there a way to find the maximum, minimum and average numbers within the identified fields. e.g. Max.....of (--(Name="Jon"),--(Category=B"),Spend) thanks Harry "Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems changing array field in formula
Bob, success once again!!
thanks Harry "Bob Phillips" wrote: =MAX(IF((Name="Jon")*(Category=B"),Spend)) etc. which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Harry Seymour" wrote in message ... this is now solved. Another question however, in a similar manner is there a way to find the maximum, minimum and average numbers within the identified fields. e.g. Max.....of (--(Name="Jon"),--(Category=B"),Spend) thanks Harry "Harry Seymour" wrote: I've created a formula =SUMPRODUCT(--(Name="Jon"),--(Category=B"),Spend) which sums the spend when columns name = Jon and category = B. I've now changed the "Spend" array to "SpendVol" array (which represents a new column) therefore use =SUMPRODUCT(--(Name="Jon"),--(Category=B"),SpendVol) But this returns #VALUE. This makes no sense as its the same formula just accessing a different column. Does anyone know why this would return this value or if there are any bugs that can occur in a file that would cause this. Thanks Harry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula : Problems with sharing workbook | Excel Discussion (Misc queries) | |||
Changing a text field to a date field | New Users to Excel | |||
Array Formula Limits (around 2800 or so) ? Lotus DSUM has no problems with it ! | Excel Discussion (Misc queries) | |||
How to keep leading zero without changing field to text field? | Excel Discussion (Misc queries) | |||
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) |