computer and math geeks, read please - Contractor Talk - Professional Construction and Remodeling Forum

Grumpy · 03-08-2004, 11:07 PM

Many of you know I use excel to estimate my projects. As a roofer we always charge more for a steeper slope. My spreadsheet has been suprisingly accurate for an average size house, though the formula used is totally flawed in theory. I realized this when trying to plug in a mansard roof.

In any case here is what I am trying to do:

a 12/12 roof is quite steep I'd like to add $25 per square, but at the same time a 18/12 is very steep, in which case I'd like to add $75 per square.

Ok so here's how I'd like it to work and keep knocking my head against the wall. In one field I enter the pitch (1-24) The math forumla computes based on the pitch variable and outputs an upcharge to the total price. The problem I am having is finding some common ground with the formula. I spent an hour inputting as many different formulas as I could think of.

Those of you who don't know what I am talking about can download the spreadhseet http://bestexteriors.com/pricing.xls

jproffer · 05-01-2005, 05:34 PM

hey Grumpy, sorry I'm about a year late looking thru the old stuff here, but what about using a table with the various pitches and a V-LOOKUP to find your upcharge. Then if you decide $25 isnt enough, just change each pitch to whatever upcharge you want.

If you're unfamiliar with V-lookups and such, it's kinda hard to explain but easy to do. I can make up a little something for you and email it to you if you want.

hatchet · 05-01-2005, 06:54 PM

Geesh - I missed this one too.
You could easily enough have it check the slope cell. if slope < 12 - x ; if 12 < slope < 18 - x+25 ; else if slope > 18 - x+75; i.e. a couple if statements.
Grumpy probably already has it figured out

jproffer · 05-01-2005, 09:38 PM

probably so...a year later..lol...sorry grump...hatchet your way would work too....i was thinking more like the following:

Slope.....Upcharge
1..............0
2..............0
3..............0
4..............0
and so on to 11
12............25
13............25
14............25
up to 17
18............75
19............75
to 24

then lookup the slope value from cell(whatever cell), and add the corresponding upcharge to the base price per square.

If statement would work too, little harder to change later on, say if you want to change your 18 criteria to 16 or 14, or $75 to $100...but it would work just as well or better if you don't plan on that happening. However works for the user, go for it.

hatchet · 05-02-2005, 08:35 AM

True.. more difficult to update but you could have it pull the slope min/max and the upcharges from a configuration page. I've done my estimating sheet like that.
I'll have to look at vlookup - haven't worked with that yet