Hello all, first time posting here (hope I am posting in the right section, sorry if I am not). I am a bit of a newbie with excel but know the basics to put together most common excel formulas, simple if, vlookup, tc. and conditional formatting.
So, here is what I need help with:
I work for a custom home building company and we have the idea to make a workbook that would automate our interior paint upgrades. The issue with a complex formula I am not sure how to achieve is based on the following pricing restraints:
Our homes on the interior are painted as follows: Trim is 1-tone throughout, walls and ceilings are painted 1 additional tone throughout. (we call this 2-tone)
If client wishes to have ceilings painted a different color than walls, we charge a premium (we call this 3-tone)
If the client buys crown molding in a room, we include 3-tone in the room as part of the crown fee.
Got it?
Formulas are set up as follows:
If client wants ceilings a different color (or 3-tones of paint throughout) we charge, lets says $1.00/ square foot. Very simple formula.
If however, the client wants 2-tone for most rooms and just wants to paint a select amount of rooms 3-tone, we charge an a-la-carte per room based on a closet, small room or large room premium.
To set up the paint schedule:
1. We instruct the client to specify a DEFAULT set of colors (they may enter 3-tone here which captures the 3-tone fee right up front SF of home x $1.00/SF). These colors will be used everywhere throughout the interior of the home, unless the client specifies to call out optional color schemes per room.
See ROW 11.
2. Client then has a series of rows (starting at row16) where they enter the room name then up to 3-tones of paint colors for the trim, wall and ceiling color in that specific room. There is also a simple drop down that acts as a selector for if crown is in the room or not.
The following fee results that I am trying to capture are as follows:
1. If the default colors are 3-tone, specific room paint colors are 3-tone and there is crown in the room, credit back the room fee.
2. If the default colors are 3-tone, specific room paint colors are 2-tone, credit back the room fee.
3. If the default colors are 2-tone, specific room paint colors are 3-tone, charge the room fee.
credit back the room fee if there is crown in result 3.
4. All other results should result in no room fee to be charged or credited.
So, how do I best capture this? Please remember, I build houses for a living, not program excel documents
Also, I know it would be easier to just not charge for this and say, ah heck... its all included! but if I did that I would go broke in no time...
I've attached my work book for reference. T16 starts where I need the formula to go. you can ignore the formulas starting in T16 and down the column, I thought I had it but found an error after using this a few times, the error is giving credits when it shouldn't View attachment INT PAINT UPGRADE SCHED - TEMPLATE.xlsx
Any help would really be appreciated.
So, here is what I need help with:
I work for a custom home building company and we have the idea to make a workbook that would automate our interior paint upgrades. The issue with a complex formula I am not sure how to achieve is based on the following pricing restraints:
Our homes on the interior are painted as follows: Trim is 1-tone throughout, walls and ceilings are painted 1 additional tone throughout. (we call this 2-tone)
If client wishes to have ceilings painted a different color than walls, we charge a premium (we call this 3-tone)
If the client buys crown molding in a room, we include 3-tone in the room as part of the crown fee.
Got it?
Formulas are set up as follows:
If client wants ceilings a different color (or 3-tones of paint throughout) we charge, lets says $1.00/ square foot. Very simple formula.
If however, the client wants 2-tone for most rooms and just wants to paint a select amount of rooms 3-tone, we charge an a-la-carte per room based on a closet, small room or large room premium.
To set up the paint schedule:
1. We instruct the client to specify a DEFAULT set of colors (they may enter 3-tone here which captures the 3-tone fee right up front SF of home x $1.00/SF). These colors will be used everywhere throughout the interior of the home, unless the client specifies to call out optional color schemes per room.
See ROW 11.
2. Client then has a series of rows (starting at row16) where they enter the room name then up to 3-tones of paint colors for the trim, wall and ceiling color in that specific room. There is also a simple drop down that acts as a selector for if crown is in the room or not.
The following fee results that I am trying to capture are as follows:
1. If the default colors are 3-tone, specific room paint colors are 3-tone and there is crown in the room, credit back the room fee.
2. If the default colors are 3-tone, specific room paint colors are 2-tone, credit back the room fee.
3. If the default colors are 2-tone, specific room paint colors are 3-tone, charge the room fee.
credit back the room fee if there is crown in result 3.
4. All other results should result in no room fee to be charged or credited.
So, how do I best capture this? Please remember, I build houses for a living, not program excel documents
Also, I know it would be easier to just not charge for this and say, ah heck... its all included! but if I did that I would go broke in no time...
I've attached my work book for reference. T16 starts where I need the formula to go. you can ignore the formulas starting in T16 and down the column, I thought I had it but found an error after using this a few times, the error is giving credits when it shouldn't View attachment INT PAINT UPGRADE SCHED - TEMPLATE.xlsx
Any help would really be appreciated.
