Forum Discussion

notanitgod's avatar
notanitgod
Copper Contributor
Nov 23, 2023
Solved

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,

 

 

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

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

    • notanitgod's avatar
      notanitgod
      Copper Contributor
      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.
      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        please share some data and expected result.

Resources