Replicate multiple if statements through a column

[notanitgod]

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,

[#2 · Accepted solution]

@notanitgod 

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))))))))))))))))

[#3]

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.

[#4]

@notanitgod 

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

[#5]

please share some data and expected result.

[#6]

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

[#7]

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

[#8]

@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.