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

Hi Daryl

I was wondering what these terms mean to see if their meanings have items we can identify to maybe incorporate the relevance to the DIM table:

CMS Cit.

BSS Cit

from what I can gather, they are Citations

Also what does the term:

CVN fee

mean and are there any features related to it that can be incorporated with the DIM table such as will some products have this fee tacked on or not depending on the product type. Thank you.

cheers

Hi Daryl

You may find in the link below a copy of your file with the answer to your question.

https://we.tl/t-E7byzUlSSC

This solution is based on the formulas in your sample workbook.

You must add/adapt/amend/modify, the formulas or tables once you fully understand this scenario/approach/solution.

SOME EXPLANATORY NOTES:

1-On a new "Helper Sheet" we created 3 Excel Tables with the 45 possibles combinations from the 2 dropdowns in column C and F on the "Recap Surplus Hrdshp Stlmnt" sheet

2- How the table works or how to set it up?

a) The first column has all 45 combinations

b) Column 2 is the case number

c) The 3rd column holds the formulas you will use for the selected case, (just for guidance)

d) We named the tables according to the column the calculations belong to.

In the picture below is "PctDiscountTable"

The formula for the Discount column (column N)

=IF(M30>0,IFERROR(CHOOSE(VLOOKUP(C30&F30,PctDiscountTable,2,FALSE),H30*0.12/365*(E30-D30)*M30,(H30*0.12/365*(E30-D30)+H30)*M30,H30*0.18/365*(E30-D30)*M30),""),"")

The VLOOKUP(C30&F30,PctDiscountTable,2,FALSE) formula will return the INDEX numbers 1,2,3, ...# from the table according to the combination case to be used in the CHOOSE function.

The CHOOSE formula will orderly select the formula and perform the calculation according to the case INDEX number ie. case combination

A similar process we followed for the two other columns

Please, find in the following links and videos more info about the formulas and tables used in the file

https://exceljet.net/excel-functions/excel-choose-function

https://www.youtube.com/watch?v=UkEItMh2Vs4&t=1s

https://www.youtube.com/watch?v=18Mrb2mEtWs&t=522s

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Hi Yea So,

You are quite correct! "Cit." is my acronym for citation; BSS and CMS are both types of citations, and for the purpose of handling them to calculate settlement or payment in full (meaning no discount given) of payoff values, they are both treated identically.

A CVN (for civil violation notice) fee (also known as a miscellaneous CVN fee) is used to include additional fees on an account, above and beyond interest. Misc. CVN Fees can be added to all product types, not just citations. This can be used to cover lien fees and/or satisfaction recording fees. We also use it when we receive a surplus check and the check is more than the total of all the liens' total debt (a/k/a TD) + our cost (see "Terms" row on cell V15 of the worksheet "Recap Surplus Hrdshp Stlmnt"). TD or total debit is defined as the LFV + interest + any lien fees (when applicable).

For instance, if we receive a surplus check for $48,000 and the total debt (without giving any reduction) is still less than $48,000, we can add the difference between the $48,000 and the sum of all the liens' total debt (again with no discount given) by using the Misc. CVN Fee bucket or column to reach the $48,000 figure. If the sum of all the liens' total debt (TD) + our cost without (giving any discount) is say, $42,000, then on one or more accounts we can add a total of $6,000 to the Misc. CVN Fee bucket or column, so that our total = $48,000. That way, we can apply the surplus funds ($48,000) to all the accounts and not have any leftover money not applied.

A quick question: what is a DIM table? I like to be clear on the terms we use.

I think I may now realize what you mean when you say in the post two above (copied, indented, bolded and colored in red font) below to be clear) when you mention intuitive, that didn't click with me before. Yes, I agree that it's more intuitive to go to a column to grab the type of data titled in that column when you need the column's data for a given calculation as opposed to the granular detail of a certain cell #. If that's what you mean by intuitive, then yes, I think that would be preferred versus grabbing a particular cell # (as long as the column correctly pulls data from the desired row[s]).

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.

Cheers! I recently got home from work, and need to eat my dinner now! Stay well, safe and strong!

Sincerely,

Daryl

Hi Jeovany,

Wow! That's tremendous! Thanks so much for all your assistance! I will need some time to review and study to understand all that you have said and done for me - please give me at least a few days to absorb what you have done. After I have had time to look at everything, I will let you know if I have additional questions or comments.

Regards,

Daryl

Hi Daryl,

A DIM table aka Dimension Table, is just a lookup table. For example you a sales table (transaction details) that lists sales people in different states or cities, you can create a DIM table (like a lookup table) the same as what Jeovany has created for your solution, a lookup table (DIM table) to look up discount rates for different products to be able to calculate the appropriate discount rate for a specific product. or calculation methods so you have 5 products with different calculation methods, so instead of hardcoding your formulas with that discount rate or interest rate, you can create a DIM table for your formulas to calculate their basis for discount or interest where you can service/maintain it without having to modify your formulas, because the calculated formulas for all your products are pretty much the same base amount * discount rate = discount, or base amount * interest rate = interest$. In all your 5 products all calculations are the same only the discount rates or interest rates are different for every product. So you can create a generic calculation even including the products that is not supposed to have a discount or interest, but since in the table their discount or interest rates are 0 then it doesnt matter if you use the same calculation formula for everything as long as the rates apply looking it up in the DIM table.

cheers enjoy your dinner