Working out a formula for problem

**pricey74** · 01-11-2009, 07:59 AM

I need help with a spreadsheet i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames.

I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames.

I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.

Thanks Pricey

**daddylonglegs** · 01-11-2009, 08:08 AM

Where do the first and last studs need to be? Would they be 400mm from the ends or something like half of that....or right near the ends?

If the studs can be between 400 and 500 then for some lengths you could have a minimum number (for 500mm spacing) or a maximum number (for 400mm spacing). Do you prefer one over the other?

**pricey74** · 01-11-2009, 08:48 AM

If the width was 2400 then the first studs would be on either end being 0 and 2400 then i would need to work out how many others needed to have the spacing between them 400mm minimum up to 500mm maximum spacing.

Thanks

**daddylonglegs** · 01-11-2009, 08:55 AM

So for 2400 you'd use 7 studs if one were required every 400mm, one each at 0, 400, 800, 1200, 1600, 2000 and 2400.

To get the minimum number required for one stud at least every 500mm you could use a formula like this

=CEILING(A1/500,1)+1

where A1 contains the length.

This would give you 6 for 2400 because with 1 at either end and 4 evenly spaced between you can get one stud every 480 which is within the 500 limit