SOLVED

Replicate multiple if statements through a column

Copper Contributor

Replicate multiple if statements through a column

Hi all,

Kind of new to all this in excel. I have a, multiple if statement in D3.

D3 Formula:

=IF(AND(J3<(D3/2),(D3/2)<K3),L3*2,IF(AND(J4<(D3/2),(D3/2)<K4),L4*2,IF(AND(J5<(D3/2),(D3/2)<K5),L5*2,IF(AND(J6<(D3/2),(D3/2)<K6),L6*2,IF(AND(J7<(D3/2),(D3/2)<K7),L7*2,IF(AND(J8<(D3/2),(D3/2)<K8),L8*2,IF(AND(J9<(D3/2),(D3/2)<K9),L9*2,IF(AND(J10<(D3/2),(D3/2)<K10),L10*2,IF(AND(J11<(D3/2),(D3/2)<K11),L11*2,IF(AND(J12<(D3/2),(D3/2)<K12),L12*2,IF(AND(J13<(D3/2),(D3/2)<K13),L13*2,IF(AND(J14<(D3/2),(D3/2)<K14),L14*2,IF(AND(J15<(D3/2),(D3/2)<K15),L15*2,IF(AND(J16<(D3/2),(D3/2)<K16),L16*2,IF(AND(J17<(D3/2),(D3/2)<K17),L17*2,IF(AND(J18<(D3/2)),L18*2,0)))))))))))))))))

I want to replicate this through the D column with the only change be that, if I am at D4, where ever it says D3 in the above example will change to D4 and so on for the rest of the D column.

I manually entered the first 6 cells in the column. When I highlight the 6 cells and try to drag down to where I want to stop, K3 in the formula changes to K9, and K4 changes to K10 and so on. It does this for cells 9 - 14, then cells 15 - 19, K3 in the formula changes to K15. During this time D3 changes correctly and matches the cell.

D9 Formula:

=IF(AND(J9<(D9/2),(D9/2)<K9),L9*2,IF(AND(J10<(D9/2),(D9/2)<K10),L10*2,IF(AND(J11<(D9/2),(D9/2)<K11),L11*2,IF(AND(J12<(D9/2),(D9/2)<K12),L12*2,IF(AND(J13<(D9/2),(D9/2)<K13),L13*2,IF(AND(J14<(D9/2),(D9/2)<K14),L14*2,IF(AND(J15<(D9/2),(D9/2)<K15),L15*2,IF(AND(J16<(D9/2),(D9/2)<K16),L16*2,IF(AND(J17<(D9/2),(D9/2)<K17),L17*2,IF(AND(J18<(D9/2),(D9/2)<K18),L18*2,IF(AND(J19<(D9/2),(D9/2)<K19),L19*2,IF(AND(J20<(D9/2),(D9/2)<K20),L20*2,IF(AND(J21<(D9/2),(D9/2)<K21),L21*2,IF(AND(J22<(D9/2),(D9/2)<K22),L22*2,IF(AND(J23<(D9/2),(D9/2)<K23),L23*2,IF(AND(J24<(D9/2)),L24*2,0))))))))))))))))

I only have one sheet open and all cells formatting is set to general.

I've basically googled how to do anything with excel including this formula. I am not sure what I am doing wrong or if this is the normal behavior for excel.

Any and all assistance will be appreciated.

Thanks...

Regards,

7 Replies
best response confirmed by HansVogelaar (MVP)
Solution

Re: Replicate multiple if statements through a column

In cell D3 the formula returns a circular reference but you can enter the formula for example in cell C3:

=IF(AND(\$J\$3<(D3/2),(D3/2)<\$K\$3),\$L\$3*2,

IF(AND(\$J\$4<(D3/2),(D3/2)<\$K\$4),\$L\$4*2,

IF(AND(\$J\$5<(D3/2),(D3/2)<\$K\$5),\$L\$5*2,

IF(AND(\$J\$6<(D3/2),(D3/2)<\$K\$6),\$L\$6*2,

IF(AND(\$J\$7<(D3/2),(D3/2)<\$K\$7),\$L\$7*2,

IF(AND(\$J\$8<(D3/2),(D3/2)<\$K\$8),\$L\$8*2,

IF(AND(\$J\$9<(D3/2),(D3/2)<\$K\$9),\$L\$9*2,

IF(AND(\$J\$10<(D3/2),(D3/2)<\$K\$10),\$L\$10*2,

IF(AND(\$J\$11<(D3/2),(D3/2)<\$K\$11),\$L\$11*2,

IF(AND(\$J\$12<(D3/2),(D3/2)<\$K\$12),\$L\$12*2,

IF(AND(\$J\$13<(D3/2),(D3/2)<\$K\$13),\$L\$13*2,

IF(AND(\$J\$14<(D3/2),(D3/2)<\$K\$14),\$L\$14*2,

IF(AND(\$J\$15<(D3/2),(D3/2)<\$K\$15),\$L\$15*2,

IF(AND(\$J\$16<(D3/2),(D3/2)<\$K\$16),\$L\$16*2,

IF(AND(\$J\$17<(D3/2),(D3/2)<\$K\$17),\$L\$17*2,

IF(AND(\$J\$18<(D3/2),(D3/2)<\$K\$18),\$L\$18*2,0))))))))))))))))

When you fill the formula down this is returned in cell C9:

=IF(AND(\$J\$3<(D9/2),(D9/2)<\$K\$3),\$L\$3*2,

IF(AND(\$J\$4<(D9/2),(D9/2)<\$K\$4),\$L\$4*2,

IF(AND(\$J\$5<(D9/2),(D9/2)<\$K\$5),\$L\$5*2,

IF(AND(\$J\$6<(D9/2),(D9/2)<\$K\$6),\$L\$6*2,

IF(AND(\$J\$7<(D9/2),(D9/2)<\$K\$7),\$L\$7*2,

IF(AND(\$J\$8<(D9/2),(D9/2)<\$K\$8),\$L\$8*2,

IF(AND(\$J\$9<(D9/2),(D9/2)<\$K\$9),\$L\$9*2,

IF(AND(\$J\$10<(D9/2),(D9/2)<\$K\$10),\$L\$10*2,

IF(AND(\$J\$11<(D9/2),(D9/2)<\$K\$11),\$L\$11*2,

IF(AND(\$J\$12<(D9/2),(D9/2)<\$K\$12),\$L\$12*2,

IF(AND(\$J\$13<(D9/2),(D9/2)<\$K\$13),\$L\$13*2,

IF(AND(\$J\$14<(D9/2),(D9/2)<\$K\$14),\$L\$14*2,

IF(AND(\$J\$15<(D9/2),(D9/2)<\$K\$15),\$L\$15*2,

IF(AND(\$J\$16<(D9/2),(D9/2)<\$K\$16),\$L\$16*2,

IF(AND(\$J\$17<(D9/2),(D9/2)<\$K\$17),\$L\$17*2,

IF(AND(\$J\$18<(D9/2),(D9/2)<\$K\$18),\$L\$18*2,0))))))))))))))))

Re: Replicate multiple if statements through a column

Bro, thank you so much. so I just need to put those \$, where you did to get this to work? Dude, I've been trying to get this working for almost 3 hours now, resorted to doing it manual, until you came along.

Can't express how much I appreciate you help. Have an amazing day dude.

Re: Replicate multiple if statements through a column

Perhaps

``=2*SUMPRODUCT( (\$J\$3:\$J\$18 < D3/2)*(\$K\$3:\$K\$18 > D3/2)*\$L\$3:\$L\$18)``

works if to add into K18 big enough value

Re: Replicate multiple if statements through a column

please share some data and expected result.

Re: Replicate multiple if statements through a column

Hi @SergeiBaklan i will also try this and and see if this works, thanks so much for your feedback.

Re: Replicate multiple if statements through a column

Hi, the solution by @OliverScheurich solved my problem exactly.

Re: Replicate multiple if statements through a column

@notanitgod Glad to hear your cell reference problem was solved. As an aside, the use of multiple nested IF formulas can be simplified here with a single lookup function, such as the INDEX/MATCH method, or the XLOOKUP function (if it's available in your version of Excel). For example:

``=IFERROR(INDEX(\$L\$3:\$L\$18, MATCH(1, (\$J\$3:\$J\$18<(D3/2))*((D3/2)<\$K\$3:\$K\$18), 0))*2, 0)``

- OR -

``=XLOOKUP(1, (\$J\$3:\$J\$18<(D3/2))*((D3/2)<\$K\$3:\$K\$18), \$L\$3:\$L\$18, 0)*2``

Note: the XLOOKUP function is only available for use in Excel 2021 or later. If you have an earlier version of Excel, the INDEX/MATCH method can be used, but may require pressing Ctrl+Shift+Enter on your keyboard to enter the formula in the cell.

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

Re: Replicate multiple if statements through a column

In cell D3 the formula returns a circular reference but you can enter the formula for example in cell C3:

=IF(AND(\$J\$3<(D3/2),(D3/2)<\$K\$3),\$L\$3*2,

IF(AND(\$J\$4<(D3/2),(D3/2)<\$K\$4),\$L\$4*2,

IF(AND(\$J\$5<(D3/2),(D3/2)<\$K\$5),\$L\$5*2,

IF(AND(\$J\$6<(D3/2),(D3/2)<\$K\$6),\$L\$6*2,

IF(AND(\$J\$7<(D3/2),(D3/2)<\$K\$7),\$L\$7*2,

IF(AND(\$J\$8<(D3/2),(D3/2)<\$K\$8),\$L\$8*2,

IF(AND(\$J\$9<(D3/2),(D3/2)<\$K\$9),\$L\$9*2,

IF(AND(\$J\$10<(D3/2),(D3/2)<\$K\$10),\$L\$10*2,

IF(AND(\$J\$11<(D3/2),(D3/2)<\$K\$11),\$L\$11*2,

IF(AND(\$J\$12<(D3/2),(D3/2)<\$K\$12),\$L\$12*2,

IF(AND(\$J\$13<(D3/2),(D3/2)<\$K\$13),\$L\$13*2,

IF(AND(\$J\$14<(D3/2),(D3/2)<\$K\$14),\$L\$14*2,

IF(AND(\$J\$15<(D3/2),(D3/2)<\$K\$15),\$L\$15*2,

IF(AND(\$J\$16<(D3/2),(D3/2)<\$K\$16),\$L\$16*2,

IF(AND(\$J\$17<(D3/2),(D3/2)<\$K\$17),\$L\$17*2,

IF(AND(\$J\$18<(D3/2),(D3/2)<\$K\$18),\$L\$18*2,0))))))))))))))))

When you fill the formula down this is returned in cell C9:

=IF(AND(\$J\$3<(D9/2),(D9/2)<\$K\$3),\$L\$3*2,

IF(AND(\$J\$4<(D9/2),(D9/2)<\$K\$4),\$L\$4*2,

IF(AND(\$J\$5<(D9/2),(D9/2)<\$K\$5),\$L\$5*2,

IF(AND(\$J\$6<(D9/2),(D9/2)<\$K\$6),\$L\$6*2,

IF(AND(\$J\$7<(D9/2),(D9/2)<\$K\$7),\$L\$7*2,

IF(AND(\$J\$8<(D9/2),(D9/2)<\$K\$8),\$L\$8*2,

IF(AND(\$J\$9<(D9/2),(D9/2)<\$K\$9),\$L\$9*2,

IF(AND(\$J\$10<(D9/2),(D9/2)<\$K\$10),\$L\$10*2,

IF(AND(\$J\$11<(D9/2),(D9/2)<\$K\$11),\$L\$11*2,

IF(AND(\$J\$12<(D9/2),(D9/2)<\$K\$12),\$L\$12*2,

IF(AND(\$J\$13<(D9/2),(D9/2)<\$K\$13),\$L\$13*2,

IF(AND(\$J\$14<(D9/2),(D9/2)<\$K\$14),\$L\$14*2,

IF(AND(\$J\$15<(D9/2),(D9/2)<\$K\$15),\$L\$15*2,

IF(AND(\$J\$16<(D9/2),(D9/2)<\$K\$16),\$L\$16*2,

IF(AND(\$J\$17<(D9/2),(D9/2)<\$K\$17),\$L\$17*2,

IF(AND(\$J\$18<(D9/2),(D9/2)<\$K\$18),\$L\$18*2,0))))))))))))))))