Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debugging SUMPRODUCT
Hi all,
After some good advice on here I'm using the SUMPRODUCT function to analyse personal expenditure data. It's scanning data on another worksheet to see if each row of that worksheet falls within the date range for the column and the catagory for the row. If it does the value gets added in. I've got a fairly lengthy formula with 3D references. =SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J $4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J $4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F $3202)) It isn't quite producing the figures I would expect. What I want to be able to do is to see what cells on the other worksheet ("Money data") are going into the sum. Is there a way to do this? Cheers! Martin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debugging SUMPRODUCT
Why are you using 0+ throughout. I think I'd change it to this
=SUMPRODUCT(--('Money Data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J$4)+1,1)),--('Money Data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J$4)+1,1)),--('Money Data'!$G$1:$G$3202=$A6),('Money Data'!$F$1:$F$3202)) -- HTH, Barb Reinhardt " wrote: Hi all, After some good advice on here I'm using the SUMPRODUCT function to analyse personal expenditure data. It's scanning data on another worksheet to see if each row of that worksheet falls within the date range for the column and the catagory for the row. If it does the value gets added in. I've got a fairly lengthy formula with 3D references. =SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J $4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J $4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F $3202)) It isn't quite producing the figures I would expect. What I want to be able to do is to see what cells on the other worksheet ("Money data") are going into the sum. Is there a way to do this? Cheers! Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debugging SUMPRODUCT
You can use conditional formatting to highlight the cells that are being
used to produce the result. On the Money sheet enter these formulas in some cells, say, J1:J3 J1: =DATE(YEAR(Sheet_name!J$4), MONTH(Sheet_name!J$4)+1,1) J2: =DATE(YEAR(Sheet_name!J$4)-1, MONTH(Sheet_name!J$4)+1,1) J3: =Sheet_name!A6 Replace Sheet_name with the actual sheet name! Now, select the range B1:G3202 Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the box on the right: =AND($B1<$J$1,$B1=$J$2,$G1=$J$3) Click the Format button Click the Patterns tab Select a nice color OK out Rows that are highlighted are being used to produce the result of your formula. -- Biff Microsoft Excel MVP wrote in message ... Hi all, After some good advice on here I'm using the SUMPRODUCT function to analyse personal expenditure data. It's scanning data on another worksheet to see if each row of that worksheet falls within the date range for the column and the catagory for the row. If it does the value gets added in. I've got a fairly lengthy formula with 3D references. =SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J $4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J $4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F $3202)) It isn't quite producing the figures I would expect. What I want to be able to do is to see what cells on the other worksheet ("Money data") are going into the sum. Is there a way to do this? Cheers! Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Debugging SUMPRODUCT
Whilst I don't like 0+, it just looks ugly, it is just as valid as -- Barb.
-- __________________________________ HTH Bob "Barb Reinhardt" wrote in message ... Why are you using 0+ throughout. I think I'd change it to this =SUMPRODUCT(--('Money Data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J$4)+1,1)),--('Money Data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J$4)+1,1)),--('Money Data'!$G$1:$G$3202=$A6),('Money Data'!$F$1:$F$3202)) -- HTH, Barb Reinhardt " wrote: Hi all, After some good advice on here I'm using the SUMPRODUCT function to analyse personal expenditure data. It's scanning data on another worksheet to see if each row of that worksheet falls within the date range for the column and the catagory for the row. If it does the value gets added in. I've got a fairly lengthy formula with 3D references. =SUMPRODUCT((0+('Money data'!$B$1:$B$3202<DATE(YEAR(J$4), MONTH(J $4)+1,1))),(0+(('Money data'!$B$1:$B$3202=DATE(YEAR(J$4)-1,MONTH(J $4)+1,1)))),(0+(('Money data'!$G$1:$G$3202=$A6))),('Money data'!$F$1:$F $3202)) It isn't quite producing the figures I would expect. What I want to be able to do is to see what cells on the other worksheet ("Money data") are going into the sum. Is there a way to do this? Cheers! Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula debugging | Excel Worksheet Functions | |||
Debugging runtime errors | Excel Discussion (Misc queries) | |||
change value while debugging | Excel Discussion (Misc queries) | |||
Need some debugging help, please | Excel Discussion (Misc queries) | |||
Startup Error debugging help | Excel Discussion (Misc queries) |