If Function with Multiple Conditions to be met first

PabloMagnifico

New Member
Joined
Jan 31, 2018
Messages
3
Using Excel 2016. Windows 7.
I'm stumped and need help. A new real estate investment property was purchased that contains vacant and occupied units. There are two kinds of vacant units: Vacant Penthouse Units (PH) and Vacant Units (or all others that aren’t PH). There are a total of 14 Vacant and Vacant PH units. There are 68 occupied units for your reference.

There are 8 different Lease Situation Descriptions that would determine what Leasing Commission (LC) % will be paid and what LC % was budgeted for:
New, No 3rd Party Broker
New, w/ 3rd Party Broker
Renewal, No 3rd Party Broker
Renewal, w/ 3rd Party Broker
New Penthouse, No Broker
New Penthouse, w/ Broker
Renewal Penthouse, No Broker
Renewal Penthouse, w/ Broker
(These are named (LeaseType_1 through 8 in the workbook)

The formula for the “Actual LC % to be Used” created works:
=IF($C$3=$C$8,6%,IF($C$3=$C$9,6%,IF($C$3=$C$10,2.5%,IF($C$3=$C$11,2.5%,IF($C$3=$C$12,6%,IF($C$3=$C$13,8%,IF($C$3=$C$14,2.5%,IF($C$3=$C$15,3%))))))))

The formula for the “Budget LC % to be Used” has been creating issues for me. I need the formula to read:
If Unit __ is located in cells J8-J13 and if the “New or Renewal Lease” (C3) is LeaseType_1 then use 6%; If Unit __ is located in cells J8-J13 and if C3 is LeaseType_2 then use 6%; If Unit __ is located in cells M8-M15 and if C3 is LeaseType_5 then use 8%; If Unit __ is located in cells M8-M15 and if C3 is LeaseType_6 then use 8%.
Those conditions need to be met first or have first priority. If those four conditions are false, then:
IF($C$3=$C$8,3.38%,IF($C$3=$C$9,3.38%,IF($C$3=$C$10,3.38%,IF($C$3=$C$11,3.38%,IF($C$3=$C$12,4.25%,IF($C$3=$C$13,4.25%,IF($C$3=$C$14,4.25%,IF($C$3=$C$15,4.25%,))))))))))))}
Basically LeaseType_1 through 4 would be 3.38% and LeaseType_5 through 8 would be 4.25%.

I keep getting #N/A.

Here's my crappy/ugly formula:

{=IF(AND(Unit=LOOKUP(Unit,J8:J13),$C$3=LeaseType_1),6%,IF(AND(Unit=LOOKUP(Unit,J8:J13),$C$3=LeaseType_2),IF(AND(Unit=LOOKUP(Unit,M8:M15),$C$3=LeaseType_5),8%,IF(AND(Unit=LOOKUP(Unit,M8:M15),$C$3=LeaseType_6),8%,IF($C$3=$C$8,3.38%,IF($C$3=$C$9,3.38%,IF($C$3=$C$10,3.38%,IF($C$3=$C$11,3.38%,IF($C$3=$C$12,4.25%,IF($C$3=$C$13,4.25%,IF($C$3=$C$14,4.25%,IF($C$3=$C$15,4.25%,))))))))))))}

Any and all help is greatly appreciated.

I’ve uploaded the workbook to DropBox (link below) but will email the work or get it to you via any method you prefer.
https://www.dropbox.com/sh/lwpxxmrzl3y9oby/AADSJ3V_PD8iyIySKaYMwJ_Ra?dl=0
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
I hope this is some help for you. Your formula is returning #N/A because the first lookup in the first tested condition is failing: the lookup returns #N/A. Excel knows #N/A is not TRUE and not FALSE, so it returns #N/A for the whole formula evaluation.

Here's what I think you mean for the second formula.
If the unit is vacant then,
if the unit is a penthouse, return 8%,
else, if the unit is not a penthouse, return 6%.
Otherwise, when the unit is occupied, lookup the value from C3 in the lease descriptions and return the matching commission.

Knowing that the list of vacant units might shrink or grow, I used the range J8:J23 for the vacant non-penthouse units and the range L8:L23 for the vacant penthouse units. Try this formula in C18:

=IF(COUNTIF($J$8:$J$23,Unit),6%,IF(COUNTIF($L$8:$L$23,Unit),8%,INDEX($E$8:$E$15,MATCH($C$3,$C$8:$C$15,0))))


You might try this alternative for your first formula:

=IF(OR($C$3=$C$8,$C$3=$C$9,$C$3=$C$12),6%,IF(OR($C$3=$C$10,$C$3=$C$11,$C$3=$C$14),2.5%,IF($C$3=$C$13,8%,3%)))

If you collapse your table in columns G and H to a single column, you could use a much simpler (less nesting) lookup formula instead of your first formula:

G
5
LC % to be Used by Owner
6
Leasing Commission
7
8
6.00%
9
6.00%
10
2.50%
11
2.50%
12
6.00%
13
8.00%
14
2.50%
15
3.00%

<tbody>
</tbody>

Either

=VLOOKUP($C$3,C8:G15,5,FALSE)

or

=INDEX($G$8:$G$15,MATCH($C$3,$C$8:$C$15,0))

would work.


As a side note, you uploaded your file after zipping it. Excel xlsx and xlsm files are already zipped files. When you zip them again, there is a possibility of the file size increasing rather than decreasing. This obviously didn't happen in this instance.
 
Last edited:

PabloMagnifico

New Member
Joined
Jan 31, 2018
Messages
3
Incredible. Your solutions to the first formula are much better than the nesting quagmire I created. I will go with the VLOOKUP solution since it would be the easiest to explain to others if need be. I personally need to experiment and practice more with the INDEX and MATCH functions.

Looks like you are spot on with the solution to my second formula. Thank you immensely. You are correct the list might shrink or grow but only shrink in the instances where those Vacant units become occupied and therefore on the second round of leasing they fall under the Weighted Average LC bucket, and grow if similar units were to be combined. Example being 2-APH and 2-BCDEPH becoming 2-ABCDEPH. That new unit would need to be manually entered into the list.

This brings up a new question/can of worms. What if a new tenant were to lease unit 2-D and unit 2-BCDEPH? One applies 6% LC and the other applies 8% LC to their rental amount. Obviously only half of the info needed has been provided, so I won't trouble anyone with that; unless you feel up to the challenge.

I can't thank you enough. You've been beyond helpful.

-P
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
I'm happy to have helped.

For your new question, I can't think of any way better to cope with the combination other than to treat them as two separate transactions.
 

PabloMagnifico

New Member
Joined
Jan 31, 2018
Messages
3
That's what I plan on doing.
Also I forgot to mention the zipped file dilemma. I believe DropBox, Box, Sharefile, etc., all throw everything into a compressed folder when downloading, even if it's only one file or if it's multiple files together. That's been my experience. Otherwise in this instance I only did a drag-and-drop into DropBox without compressing anything prior. So the compressing was out of my hands.

Thanks again for everything. I'm sure I'll be posting more questions in this forum as time goes on.
 

Forum statistics

Threads
1,082,342
Messages
5,364,777
Members
400,815
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top