Improving on two formulas that create length and quantity

[stan101]

Hi all,

I am looking to to improve and simplify two formulas in an old spreadhseet that I am making changes to. In a nutshell:

I have an actual length value of something. (B8)
If that length is longer than the maximum allowed length (B3), I need to add extra length for an overlap. I use the formula in cell B11 for this. I use formula in cell B10 to calculate the total quantity.

My formulas work but are quite cumbersome. I am wondering if anyone has any more elegant solutions to my issue. I am not adverse to using VBA to achieve it, but I still do not know a way to do it elegantly in VBA.

I also have many different columns and different variables for differing lengths over many sheets. So fixed formulas seem the easiest way for me to go to remember what I am doing. I have attached a basic working excel sheet with my current formulas.

Formula for B11:

=IF(B7="","",SUM(IF(B8>(7*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP(((B8/8)+VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE)),-2),IF(B8>(6*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP(((B8/7)+VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE)),-2),IF(B8>(5*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP(((B8/6)+VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE)),-2),IF(B8>(4*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP(((B8/5)+VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE)),-2),IF(B8>(3*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP(((B8/4)+VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE)),-2),IF(B8>(2*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP(((B8/3)+VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE)),-2),IF(B8>(1*VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),ROUNDUP(((B8/2)+VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE)),-2),ROUNDUP(B8,-2))))))))))

Formula for B10:

=IF(B8>(2*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP((((2* VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))+B8)/ VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),0),IF(B8>(2*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP((((2* VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))+B8)/ VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),0),IF(B8>(2*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP((((2* VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))+B8)/ VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),0),IF(B8>(2*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP((((2* VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))+B8)/ VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),0),IF(B8>(2*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP((((2* VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))+B8)/ VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),0),IF(B8>(2*(VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE) - VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))),ROUNDUP((((2* VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))+B8)/ VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),0),IF(B8>(1* VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),ROUNDUP((((1* VLOOKUP(A$1 & " Overlap Length",$A$3:$B$6,2,FALSE))+B8)/ VLOOKUP(A$1 & " Max Length",$A$3:$B$6,2,FALSE)),0),1)))))))

If anyone can assist, I would be grateful.