Forum Discussion
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,
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))))))))))))))))
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
Hi SergeiBaklan i will also try this and and see if this works, thanks so much for your feedback.
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))))))))))))))))
- 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.- please share some data and expected result.
