IF formula pattern

[blaugrana9]

Guys,
I have the following problem:
When i drag these
=IF(B4>0;A4,)
=IF(c4>0;A4,)
=IF(d4>0;A4,)
down i get:
=IF(B7>0;A7,)
=IF(c7>0;A7,)
=IF(d7>0;A7,)
and so on, it increments by 3.
Can someone please help me and tell me how to drag it down but so it can be like this:
=IF(B5>0;A5,)
=IF(c5>0;A5,)
=IF(d5>0;A5,)

and so on until 500....

[XOR LX]

Hi,

Maybe this in your first cell and drag down (not in groups of three, though; just this):

=IF(INDEX(INDEX(B:D;;IFERROR(1/(1/(MOD(ROWS($1:1);3)));3));3+ROUNDUP(ROWS($1:1)/3;0))>0;INDEX(A:A;3+ROUNDUP(ROWS($1:1)/3;0)))

Regards

[ramananhrm]

Please try this file.

[blaugrana9]

Hi,

Maybe this in your first cell and drag down (not in groups of three, though; just this):

=IF(INDEX(INDEX(B:D;;IFERROR(1/(1/(MOD(ROWS($1:1);3)));3));3+ROUNDUP(ROWS($1:1)/3;0))>0;INDEX(A:A;3+ROUNDUP(ROWS($1:1)/3;0)))

Regards

Thank you very much.

I believe this works.

Just, how do i do it for a single cell etc.

c4,b4,d4

and then drag it down to 500 to be like c5,b5,d5 etc....

cheers!

[XOR LX]

To whom are you replying?

Regards

[ben_hensel]

My approach would be different: use ROW to build indirect references that increment in threes.

Please Login or Register  to view this content.

You'll have to change the constants if you use a different output range, like E5:E505 or something.

[XOR LX]

"Just, how do i do it for a single cell etc."

Just enter that formula in your first cell and then copy down.

Regards

[blaugrana9]

I used XOR LX's solution and it works perfect!

As for the "Just, how do i do it for a single cell etc.", my actual question was how to continue a pattern of b4,c4,d4 to b5,c5,d5 to b6,c6,d6 and etc..you misunderstood my question xor lx.

But i figured it out so thanks a lot XOR LX! and everbody else for your contribution!

Cheers!

[XOR LX]

Ah, apologies for not understanding your follow-up question.

And you're welcome!