Confused

[ExcelChampion]

at the bottom of the page when you follow the link is a workbook example using DSUM.

http://www.ozgrid.com/Excel/sum-every-2nd-nth-cell.htm

I am thoroughly confused by it. The criteria the DSUM is using is either a TRUE or FALSE, but somehow its reading these TRUEs and FALSEs in a way that is allowing it to sum every nth row.

I need an explanation....

 

[pgc01]

Hi

The criterion in E2

=MOD(ROW(A2),$C$2)=0

will be true for each row whose number is multiple of C2. So, if C2=4, DSUM will sum A4+A8+A12...

Hope it's clear
PGC

 

[ExcelChampion]

Yes, but...

The result in E2 is only either TRUE or FALSE.

However, if you select 2 from the dropdown, it sums every 2nd row, and the result in E2 is FALSE.

If you select 3 from the dropdown, it sums every 3rd row, but the result in E2 is still FALSE.

So how does the DSUM know to SUM every nth row, for any number of rows you specify, when it would appear the result in e2 is only either TRUE or FALSE?

And remember, the DSUM is pointing to E2 for the criteria...which is eaither only TRUE or FALSE.

 

[pgc01]

The result in E2 is only either TRUE or FALSE.

No, that's not true. The formula you write in the criteria is not a real formula, it's like a template.

With the database functions you use the address of the first record to write a formula that will be applied to each record but modified according to its relative position.

The first record is in row 2 as row 1 would have the header, that is why you use the address A2 for the formula (template).

As you apply the formula to each record the formula is changed according to it's relative position.

It's like you write the formula in B2 and then you copy the formula down. In row 3 excel considers the formula as =MOD(ROW(A3),$C$2)=0, and so on.

Hope it's clear
PGC

 

[ExcelChampion]

Yep, crystal!

Wow, I didn't know that is how it worked. That is really neat!

Are the database functions expensive, volatile, etc?

Edit: the light bulb is really turning on now...that is why they mention using relative references and then absolute references...cool.

As you apply the formula to each record the formula is changed according to it's relative position.

 

[pgc01]

The database funtions are very fast. In a problem posted some months ago with more than 50000 rows to change from an array formula to a DSUM meant an execution more than 100 time faster! So for a very big quantity of data the database functions are a good option to consider.

There is another way of defining the formulas in database functions. You can use the headers.

This makes the formula much easier to understand but Microsoft has a bug and although it works perfectly well it displays the error #NAME?

I had a good laugh with what Microsoft wrote in the help:

When Microsoft Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion, you can ignore this error because it does not affect how the list is filtered.

In this case if A1 had "Val" the formula in E2 would be

=MOD(ROW(Val),$C$2)=0

Best regards
PGC

 

[ExcelChampion]

100 times faster? Hard to believe. Why do we not see them used on the message boards more often then?

I'll have to test it out to see for myslef.

 

[Aladin Akyurek]

100 times faster? Hard to believe. Why do we not see them used on the message boards more often then?

I'll have to test it out to see for myslef.

When you need just a single formula, it's easy to set up. If the formula is needed in multiple cells, the setup is alltogether a diffrent story:

http://www.mrexcel.com/board2/viewtopic.php?t=60895

Would you check the DSUM results for every Nth with:

=SUMPRODUCT(--(MOD(ROW(A2:A11)-ROW(A2),C2)=0),A2:A11)

for different values in C2?

The test would be easier if you converts the Numbers area into a list by means of Data|List|Create List.