Single Formula Required to copy across large table

**Ross84** · 02-13-2009, 01:46 PM

I am creating an excel document which needs to calculate the costs of wooden frameworks for canvas printing. Canvas printing frameowrks are constructed from strecther bars which are supplied in pairs. For example if you wished to construct a frame 8" x 10" you would need to purchase an 8" frame pair and a 10" frame pair to complete the frame. I have so far put together the attached which shows the costs for the strecther bar pairs. This link will help explain http://www.lionpic.co.uk/catalog/Str...-Bars,287.aspx

In addition the darker coloured boxes in the table are for larger frames which require cross braces to strengthen the frame. These are sold as single bars as also shown on my spreadsheet.

I do not have a vast knowledge of using excel so any help would be much appreciated. Email address if required is [email protected].

Thanks

**DonkeyOte** · 02-13-2009, 01:57 PM

Would this work for you...

Please Login or Register  to view this content.

Others may suggest a Sumproduct type approach but I would argue SUMIF to be more efficient in this instance (though any sort of LOOKUP, INDEX/MATCH approach would work equally well given 1:1 relationship between price & size)

**shg** · 02-13-2009, 02:09 PM

Here's the formula for the stretchers.

I don't understand the rule for braces -- if you have an 8 x 30 frame, how many braces do you need -- one of each size?

**Ross84** · 02-16-2009, 04:37 AM

if you have an 8 x 30 inch frame you will not need a brace as braces only start at 10 inch. If you had a 10 inch x 30inch frame you would require 1 no 10 inch brace.

**DonkeyOte** · 02-16-2009, 04:52 AM

My original solution assumed 2 braces were required where larger of dimensions >= 30 inches. However, your latest post implies a brace is only required based on smaller of dimension where larger >= 30 inches, in which case perhaps the attached.