Formula not working in one row but OK on other rows

[swarfmaker]

The problem cells are in row19 colunm J & K
The formula totals the current row and the three previous rows, colunm D and column G
this works on row 20 but not in row 19.

this is the formula:

=IF(MOD(ROW(),4),"",SUM(OFFSET(D19,,,-4))) does not work

=IF(MOD(ROW(),4),"",SUM(OFFSET(D20,,,-4))) This works


Many thanks for your attention.

Iain

[6StringJazzer]

Your formula does exactly what is was written to do. However, there is not enough information to understand what it is you really want to do. What result do you want instead of what you are getting?

Your formula starts with

IF(MOD(ROW(),4)

This is not a Boolean expression, that is, it does not return TRUE or FALSE. It returns a number. If the number is 0, it is treated as FALSE. Anything else is treated as TRUE.

When this formula appears in row 20, MOD(ROW(),4) is 0, which is interpreted as FALSE, and you get the SUM as the result.

When this formula appears in row 19, MOD(ROW(),4) is 3, which interpreted as TRUE, and you get a null string as the result.

Why do you have this test at the beginning of the formula?

[swarfmaker]

The IF test is to ensure that the formula is only applied when the row is populated.

I am trying to create subtotals every 4th row only when that row has been populated.

Utility meter readings are taken and recorded every week.

This is the first time I have touched Excel since I retired 12 years ago and my 75 yearold neurons are not what they were.

Any help you can give will be gratefully recieved.

Iain

[6StringJazzer]

If you want subtotals every 4th row, then you want data in row 19, 23, 27, etc., and not in 20.

I am thinking you want this in J19, then fill down:

Please Login or Register  to view this content.

[Croweater]

[deleted - didn't read the requirement properly]

[swarfmaker]

If you want subtotals every 4th row, then you want data in row 19, 23, 27, etc., and not in 20.

I am thinking you want this in J19, then fill down:

Please Login or Register  to view this content.

Thank you

Iain

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.