Problem with a increment

[Ismael]

Hi to all,

I have a problem with a increment of rows, that you guys can see in the attach file, I think is a simple one.


If any of you guys can check this for me I'd appreciate.

Best regards,


Ismael

[mvidas]

Hi Ismael,

Starting in F5, enter the formula:

=IF(LEN(B5)>12,VALUE(RIGHT(B5,2))/INDIRECT("C"&(INT((ROW()-4)/19)*19+4)),"")

Should be all set! Let me know if you have any questions as to how that works
Matt

[Ismael]

Hi Matt,

The formula works perfectly, but I don't understand the last part (INDIRECT("C"&(INT((ROW()-4)/19)*19+4)), I put this in G5 and pull down until G22 and the result is always the same (78), how this works???

Thanks for the help,

Best regards,

Ismael

[mvidas]

Sure thing!

INDIRECT("C"&(INT((ROW()-4)/19)*19+4))

INDIRECT lets you enter a string address into the function, and return that cell's contents.


The main part of this is
INT((ROW()-4)/19)*19+4


ROW()-4 - Returns the current row number, less 4.


(ROW()-4)/19 - Same as above, but divides that by 19


INT((ROW()-4)/19) - takes just the integer portion of the answer from the line above
For example, if used in row 5, it would be the same as doing
INT((5-4)/19),which would be 0. The answer would actually be 0 from rows 5-22, as for each of those rows, the row()-4 would be 1-18. Dividing any number between 1 and 18 by 19 will be less than zero, so taking the
INT() of that answer would be 0. From rows 24-41, the row()-4 would be 20-37, and dividing any of those numbers by 19 would give you a 1.xxx number


INT((ROW()-4)/19)*19
Taking the integer answer from the previous step and multiply it by 19. So rows 5-22 would still return 0*19, or zero. 24-41 would return 1*19, or 19. And 43-60 would return 2*19, or 38 (etc).


INT((ROW()-4)/19)*19+4
Adding 4 to the number from the previous portion would give you 4 for rows 5-22, 23 for rows 24-41, 42 for rows 43-60, etc.

Combining the number from the previous portion with INDIRECT, gives you the same as
INDIRECT("C4") for rows 5-22, INDIRECT("C23") for rows 24-41, etc.

Make sense? Let me know if not!
Matt

[Ismael]

Ok Matt,

Now make sense, thanks for the explation.

regards,

Ismael

[Jacob Hilderbrand]

Ismael

When I find a formula that I am not sure what it is doing I use Evaluate Formula from the Formula Auditing toolbar. It is really nice and will allow you to step through the forumula and see how it evaluates at each step.

This is also good if you have a formula that is not working as it allows you to see exacly what the values are as the formula is evaluated. :2p: