# Thread: Complex nesting IF statement? <newbie here>

1. ## Complex nesting IF statement? <newbie here>

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 INT PAINT UPGRADE SCHED - TEMPLATE.xlsx

Any help would really be appreciated.

2. Can you detail one or ytwo lines that are returning the wrong result, and explain in business terms why these are wrong?

3. I cant really put my finger on the error without looking back at my notes. The general rule here is:

1. If we charge for 3-tone on the home, we only credit back if a room is 2-tone or has crown.
2. If the default home is 2-tone, we then need to charge per room if it is specified as 3-tone, but if there is crown in the room, they get the 3-tone in that room at no charge.

In both scenarios we are trying to avoid double charging.

Basically, I think I need a nested IF statement for the following:

1. IF E11<>H11, IF E16<>H16 AND IF P16="Y", CREDIT M16
2. IF E11<>H11 AND E16=H16, CREDIT M16
3. IF E11=H11 AND E16<>H16, CHARGE M16
4. IF E11=H11, E16<>H16 AND P16="Y", CREDIT M16
5. ALL OTHER RESULTS SHOULD EQUAL ZERO

4. You think is not really a lot to go on, we could be here forever, we need to know the EXACT rules.

Also, those rules don't make sense to me, why would the default colours (row 11) affect whether to credit or charge?

5. "I think" was in terms of what type of formula (or more likely something I honestly don't know exists in excel) "I think" I need.... if I knew what formula or how to work this into excel properly, I wouldn't have posed this question to this forum. I figured I would ask some pros how BEST to do this.

The default colors are based on the whole house, which is a simple equation of the square footage (SF) of the home multiplied by \$1.00/SF. (see row 11)

EXAMPLE: if the home is 3,000SF and the client wants all ceilings in the home painted a 3rd tone, we would charge them 3,000SF x \$1.00/SF or \$3,000.00

Therefore, if we charge the client for the entire home in 3-tone, we should credit back any room that the client bought crown molding in (because as stated earlier, we automatically include a 3-tone ceiling in the room the client buys crown in).

The room fees are:
Closest - \$75
Small Rooms - \$150
Large Rooms - \$300

So when the default is 3-tone, we only credit back room fees (if they designate a room as 2-tone or there is crown).

When the default is 2-tone, when the client designates a 3-tone selection per room, we charge the room fee. (if however this room has crown, we dont charge in this specific scenario).

You can see an example of all of this in the excel file in linked in the OP.

I'll completely express the EXACT rules again in words only:

First - If "E11" does not equal "H11", "E16" does not equal H16 and P16 equals "Y", multiply "M16" by "-1"
Second - If "E11" does not equal "H11" and "E16" equals "H16", multiply "M16" by "-1"
Third - If "E11"="H11" and "E16" does not equal "H16", multiply "M16" by "1"
Fourth - If "E11" equals "H11", "E16" does not equal "H16" and "P16" equals "Y", multiply "M16" by "-1"
Fifth - All other results should equal "0"

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•