Cannot make nested if's work

I have a formula that works but is incorrect. It is for calculating how many 2x4's I need to build a wall.
On a sheet called Tasks I have the following:
Cell use
B7 Build wall Yes or No
B8 4" or 6" wall
B9 length of wall in inches
B10 height of wall in inches
B20 Need bottom plate to be pressure treated? Yes or No

On another sheet called Projectmaster I have the following in cell C9, which determines how many 8' long 2x4's are needed:

=IF(AND(Tasks!B7="y",Tasks!B8=4,Tasks!B9>0,Tasks!B10<97),ROUNDUP(((Tasks!B9/96)*2)+(Tasks!B9/16)+1,0),0)

In cell C10 I have this, which determines how many 10' long 2x4's are needed:

=IF(AND(Tasks!B7="y",Tasks!B8=4,Tasks!B9>0,Tasks!B10>96,Tasks!B10<121),ROUNDUP(((Tasks!B9/96)*2)+(Tasks!B9/16)+1,0),0)

Now these formulas work OK, but if the wall is longer than 96" I would rather use longer top and bottom plates, i.e. a 120" wall could use two 10' plates instead of two 8' and two 2' pieces. Same applies if the wall is longer than 10' I would want to use 12' plates.

I tried adding an if statement in front of the "Tasks!B9/96)*2)" statement, but keep being told formula contains an error.

Can anyone suggest how I can test the length of the wall inside this formula and choose the correct length based on Tasks!B9?

As an example, a 8' high wall that is 10' long should have nine 2x4x8' and two 2x4x10'.

Or would a lookup table be better to use?

Attach a Workbook

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"