Excel nested if, and and or statements testing for 1 or more items in a group, and comparing to 1 or more items in a second group to give a reply in one cell

Here's my Final Version of the solution:

Click here to get file => Solution

Screen Shot:

Discount% formula

Discount$ formula

Interest Charge amount formula

Settlement Total$ formula

Sheet view:

When US, MH, Rem. is selected in drop down 1,

drop down 2 only displays the relevant selections:

as is with BSS Cit. and CMS Cit.

Data Validation for the Citation fee, user or you can't enter an amount in that field in error:

And lastly, the lookup (DIM) table the 9 permutations of calculation types all in 1 table so you wont have that long winded IF(AND(OR))) function, plus the formulas above look more elegant and easier to maintain just like changing fuses (don't bury the fuse underground with the pipes and cover it in concrete, use a fuse box):

I know you already have a solution but here's an alternative that you can keep for your reference.

cheers!!

Hi Daryl,

Re: Trim, autocalc, [#All] and iferror are examples.

I was using trim since the formulas was generating "FALSE" and thought maybe it is registering a FALSE because the value in F30 might have leading or trailing spaces, so as I was testing the new formulas I wrapped a TRIM around cell references temporarily to see if that was the problem and just forgot to remove it after.

The autocalc is just what i named the sheet containing the dim table, (see the sheet name of the dim table)

IFERROR, you can wrap it around formulas that generate an #N/A, or a #VALUE just to be able to put a default value to the erring formula so instead of putting a "", I can put "need further investigation", or a zero value.

You can put data validation on cells that must not be filled out because of the calculation method:

below is a data validation rule that prevents the user from inputting any value unless the value in F30 equals ME-C, MH-F3, XH-C, or XH-F3

if the user tries to enter a value, excel generates an error message:

and when they press cancel the value gets removed from the cell.

Anyhoo, you got your solution, don't hesitate to reach out if you have any questions, and don't forget to mark Jeovany's response as the answer.

cheers

Hello Yea So,

I cannot thank you enough for your kindness and brilliance!

It will take me a little time to properly absorb your various points. I will need to learn about the different functions you have incorporated into your formulas. Trim, autocalc, [#All] and iferror are examples. For instance, Trim is used to remove extra spaces from a text string. I don't quite follow why that is needed since the drop-down lists (my acronym [ddl]) specify the text, which should prevent the user from manually typing in the product/calc type and not having it properly identified by the formula.

In the screen shot below you mention that IF3 calc type does not specify the interest discount rate. This is something that the user would select based on what is needed to have the total of our adjustments on multiple liens equal the surplus check that we receive. For example, 30 liens' full interest calculation + our 30% fee may equal $325,000. If we apply extreme hardship calculations to the 30 liens, the total may drop to $35,000. However, we have (in this make-up example) received a surplus check for $48,000. We want to have the total of all 30 liens adjustments = $48,000.

As stated above, I start by using XH (extreme hardship) calculations as they are the simplest mathematically and do not involve any interest calculations. If I need to increase the total to go beyond the XH level, I begin by charging interest with no discounts to get closer to the check amount. If there are a minimal number of liens and the total adjustments are too small compared to the check, I will add the difference to the Misc. CVN Fee bucket/column. However, If the adjustments with full interest and not discounts (with our 30% fee) total more than needed, I start by applying discounts to get the adjustment totals closer to the check amount. The famous 3 (F3) products (US, MH, Rem) have a simper discount calculation method then the citations, of only taking the interest charge and multiplying it by a discount percentage, then adding the FV + interest charge - interest discount = TDD (total discounted debt), X 30%; and adding the 30% figure to the TDD.

This is why I say the interest discount % for the IF3 calc type is user driven and is why I have color coded in light blue the interest discount column. We can do the same for citations, except that we take the LFV + int. = TD; we multiply the TD by the discount percentage; this = TDD or total discounted debt; on top of that we add 30%.

For citations, if we are using a discount of 24% or higher, we add the 30% to the TDD. However, for citations if we use a discount of 23% or less, we do not add the 30% on top of the TDD. Instead, we reduce by 30% what we pass through to the originating department of the debt (they are the ones who have referred the debt to us for collection purposes). Using a discount for citations between 23% and 24% requires more scrutiny, so to be simple, I prefer to use either below 23% or above 24% discounts for citation-based liens.

As a side note, the liens are based on County code enforcement or housing safety types of violations. A warning is issued, if not fixed timely, a ticket is issued; if not paid and brought into compliance timely (the cause of the ticket is fixed), it goes through due process and eventually becomes a lien that earns interest at either 12% or 18% per annum, with the interest beginning to accrue when the lien is recorded in the public records' database. Liens can accrue interest for up to 20 years.

The liens are further divided into citation-based liens and housing standard violation liens. The citation liens are of two varieties: BSS for structural-related issues and CMS for County Code violation issues. If the violation relates to housing safety standards not being met or other safety issues for example, then the violation can fall into different categories - Unsafe Structure (US) and Minimum Housing (MH) liens. If the County has to spend money to fix or otherwise remediate the cause of the violation, then they send a bill to the violator; if it is not paid timely, it goes through due process and eventually become a lien if not paid timely ... the lien is called a Remediation (Rem.) type of lien. Like remediation liens, if the County has to spend money to demolish an unsafe structure, they send a bill to the violator, and the same process like the remediation lien ensues. Minimum housing (MH) liens occur when minimum housing safety standards are not met and follow the same sort of process. You get the idea.

With the exception of the MH liens, the rest of the lien products (BSS and CMS citation-based liens, US and Rem. liens) all accrue interest at 12% per annum. MH liens accrue interest at 18% per annum. When I was writing the formulas, I was attempting group all the 12% interest earning products together, and use the final negative statement to cover the 18% interest calc for the MH lien product when matched with the IF3 calc type.

Regarding using "" or IsBlank function and how using double quotes "" causes problems, is there any other way to have the interest and discount columns remain blank to see when using the extreme hardship XH calc, which does not use any interest in its calculation? I had copied the formulas for interest charge amount and interest discount amount down the column, but didn't want to see "FALSE" if that row calls for an XH calc type.

By the way, what happened to your testing of columns that you mentioned in an earlier post, where you were asking about it being more intuitive (see below copy of your post)?

Hi Daryl,

I'm testing the body of the form to be a table so the formulas can be written this way:

=IF(

OR([@CalcType]="23IDFNA",[@CalcType]="24IDFA", [@CalcType]="IF3",[@CalcType]="ME-F3"),

[@BaseAmt]*0.12/365*([@GdThru]-[@RecDate])*[@[Disc%]],

[@BaseAmt]*0.18/365*([@GdThru]-[@RecDate])*[@[Disc%]])

Do you think it's more intuitive than referencing cells but instead referencing the column name for the calculation of that row? I'm still trying it since I've never used it before.

Well, that's all I have time for tonight, and my coffee has worn off! Will go have some salad and protein for a light supper! Sorry if I rambled a bit as I tried to cover some of the questions your brought up.

Have a good night or if it's day when you read this, a good day!

Sincerely,

Daryl

Hi Daryl,

Here is the file => Cheers

As a note after I studied the interaction of your setup,

there are really only 9 permutations because the function of the first drop down list is only to determine what calculation type to use, after that the values in the first drop down list has served its purpose there. So the table/fuse box or your dim table should only consist of a table like this:

the function of the second drop down list is to determine what the variables of the IntChg (interest charge), IntDisc (interest discount, CitationFee, and the 30% Fee

so the calculation should be:

TD = LFV +Interest = VLOOKUP(F30,AutoCalc[#All],2,0)

for interest charge

Interest discount calc =

=IF(M30=0,0,

IF(M30>0%,

IF(

OR(TRIM(F30)="MH-F3",TRIM(F30)="IF3"),

H30*VLOOKUP(TRIM(F30),AutoCalc[#All],2,0)*(E30-D30)*M30,

IF(

OR(TRIM(F30)="23IDFNA",TRIM(F30)="24IDFA"),

(H30*VLOOKUP(TRIM(F30),AutoCalc[#All],2,0)*(E30-D30)+H30)*M30,

0

))))

the above formula should be the only formula in the interest discount cell and it should cover the .12/365 and the .18/365 rates because the VLOOKUP formula looks for the appropriate calculation type so the formula below after implementing the dim table becomes long winded and obsolete the rates are hardcoded and difficult to maintain (good thing theres only 15 cells you have to edit to update or change the rates:

=IF(ISBLANK(M30),0,

IF(M30>0%,

IF(

AND(

   OR(TRIM(C30)="US",TRIM(C30)="Rem."), 

   OR(TRIM(F30)="MH-F3",TRIM(F30)="IF3") 

  ), 

H30*0.12/365*(E30-D30)*M30,

                                                                                 IF( 

AND(

   OR(TRIM(C30)="CMS Cit.",TRIM(C30)="BSS Cit."), 

   OR(TRIM(F30)="23IDFNA",TRIM(F30)="24IDFA") 

   ), 

(H30*0.12/365*(E30-D30)+H30)*M30,

H30*0.18/365*(E30-D30)*M30

))))

This formula below is kind of iffy

=IF(ISBLANK(M30),"",

IF(M30>0%,

IF(

AND(

   OR(TRIM(C30)="US",TRIM(C30)="Rem."), 

   OR(TRIM(F30)="**MH-F3**",TRIM(F30)="**IF3**") 

  ), 

H30*0.12/365*(E30-D30)*M30,

IF(

AND(

   OR(TRIM(C30)="CMS Cit.",TRIM(C30)="BSS Cit."), 

   OR(TRIM(F30)="23IDFNA",TRIM(F30)="24IDFA") 

   ), 

(H30*0.12/365*(E30-D30)+H30)*M30,

H30*0.18/365*(E30-D30)*M30

))))

because after the 4th IF function (the green text) there is nothing left for the else part calculation to do.

If you notice the bolded MH-F3 and the bolded IF3, MH-F3 says in the terms that there is a 35% interest discount:

Whereas the IF3 does not state what the % of interest discount is:

The Interest Charge Calc:

=IFERROR(IF(

OR(TRIM(F30)="ME-C",TRIM(F30)="ME-F3",TRIM(F30)="XH-C",TRIM(F30)="XH-F3",TRIM(F30)="mh-LCA"), 

0,

IF(

OR(TRIM(F30)="MH-F3",TRIM(F30)="IF3"),

H30*VLOOKUP(F30,AutoCalc[#All],2,0)*(E30-D30),

IF(

OR(TRIM(F30)="23IDFNA",TRIM(F30)="24IDFA"),

(H30*VLOOKUP(F30,AutoCalc[#All],2,0)*(E30-D30)+H30),

H30*VLOOKUP(F30,AutoCalc[#All],2,0)*(E30-D30)

))),0)

The Settlement can be replaced like below:

=IF(TRIM(F30)="",0,

IF(

OR(TRIM(F30)="ME-C",TRIM(F30)="ME-F3",TRIM(F30)="XH-C",TRIM(F30)="XH-F3"),

((H30-I30+J30)+(H30-I30+J30)*VLOOKUP(F30,AutoCalc[#All],5,0)),

IF(

TRIM(F30)="MH-LCA",

(H30*VLOOKUP(F30,AutoCalc[#All],5,0))+H30,

IF(

TRIM(F30)="MH-F3",

(H30+O30-N30-L30)+((H30+O30-N30-L30)*VLOOKUP(F30,AutoCalc[#All],5,0)),

IF(

TRIM(F30)="23IDFNA",

H30+O30-N30-L30+K30,

IF(

TRIM(F30)="24IDFA",

(H30+O30-N30-L30+K30)+((H30+O30-N30-L30+K30)*VLOOKUP(F30,AutoCalc[#All],5,0)),

IF(

TRIM(F30)="IF3",

(H30+O30-N30-L30+K30)+(H30+O30-N30-L30+K30)*VLOOKUP(F30,AutoCalc[#All],5,0)

)))))))

I hope you finish your project with great success

Cheers

Hi Daryl,

Oh and by the way regarding your formulas, if your formulas are dealing with integers, floating points(decimal numbers) anything with number data type formulas ... please stop using this: ""

as it will create problems for your calculations use: 0 instead.

cheers