This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Good evening,
I have a spreadsheet where there are two columns, one that is abeled "products" with a drop-down list of 5 items in it that the user can select, and another column labeled "calculation types" with another drop down list with a different drop-down list of 9 items for the user to select. In both cases, the user can only select one item for each column. I used If, Or and AND functions, trying to say: if any one or more of the product list group is selected AND it is matched with any one or more of the calculation type list, the it should perform a specific math function. It seemed to work well, but when I began to test it for the various permutations possible, it gave some wrong results. I have created formulas to address all five product type matches with all nine calculation types. The cells I need formulas for are "interest discount $ amount", "interest charge $ amount" and "settlement value". Depending on the selection, in certain cases the discount interest is not used in the calculation, and therefore I want those cells to remain blank. To account for the quantities possible, I am using nested functions. I am wondering if it would be better to use the IFS function, so as not to have to deal with many nesting layers. My work PC uses Office 365. so I should be able to use the IFS function there. If I cannot successfully troubleshoot my nested formulas, I thought a workaround may be to create a hybrid name for product and calculation type that the user would select from the drop-down list, and then create specific results for each hybrid name. For calculations not involving interest at all, I would like the cell to remain blank and not say "false" or give any other message. Any assistance that you could provide would be greatly appreciated.
IF(OR(C30="CMS Cit.",C30="BSS Cit.",AND(F30="mh-LCA")),
(H30*0.3)+H30,
Sincerely,
Daryl
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Sounds interesting. Will have to play around with it to be more fluent with the look up tables. But simpler is better, I definitely agree.
Thank you!
Good night!
Daryl
Yes, except that there r 9 types of calculation types, grouped into 4 types.
1 and 2 can be combined: extreme hardship & management exception for
each product combination - BSS & CMS citations r grouped together, and the F3 are grouped together (except for calculating interest for MH, which is the only type that calculates at 18% [the rest are at 12%] - F3 is the acronym for the famous 3 for US, MH & Rem product types.
Essentially, 2 groups of products (citations & F3 violations) and they match to the 3 types of calculation (me for management escalation & xh for extreme hardship, neither of which included any interest calculations); mh for moderate hardships (citations do not earn interest but the F3 group do earn interest with a 35% interest discount applied); and fi (full interest earned with or without any discount, calculated slightly differently for citations if the discount is more or less than 24% … and full interest for the F3 group with or without a discount where the cost is added to the rest (in the citations, the cost may or may not be added to the total based on the % of discount given)
These are how 2 groups of products match to 3 groups of calculation types depending on the level of hardship involved.
The worksheet is designed to calculate up to 45 accounts, although it is common to see 20-30 grouped per folios so that you can (relatively) quickly see the total due and make adjustments as desired. Currently there are separate adjustment sheets per 6 accounts, but I want to pull the #s from the worksheet and have them populate a 2nd tab automatically, thereby eliminating duplicate input, saving time and reducing errors.
We also have simpler products to calculate, but by comparison, they r fairly straightforward and easy to calculate.
Hope you understood most of my long winded explanation!
Thanks again for your input- I appreciate it.
Good night!
Daryl
Hi Daryl,
I see you have 3 drops downs, so there is 1 AND then 3 ORs inside the AND
so the statement would go:
IF(
AND(
OR(DropDown1),
OR(DropDown2),
OR(DropDown3)),
ThisIsTheCalculation
)
Looks like the formula can be made simpler if we used a lookup table of the combinations and the formula will just be as you see above.
lets see if that can be done.
cheers
Good morning,
Your point is well taken- late yesterday I saw a response from Ossie requesting the workbook. I will respond later today or tomorrow as soon as I have time to upload. Thank you for your input.
Daryl
Hi Daryl,
To clarify
Is your formula trying to say this:
IF the answer is yes, then the formula should look like this:
IF(
AND(
OR(C30="US",C30="mh",C30="Rem.",C30="bss cit.",C30="cms cit."),
OR(F30="me-c",F30="me-f3",F30="xh-c",F30="xh-f3")
),
((H30-I30+J30)+(H30-I30+J30)*0.3),
IF(
AND(
OR(C30="CMS Cit.",C30="BSS Cit."),
OR(F30="mh-LCA")
),
(H30\*0.3)+H30,
IF(
AND(
OR(C30="US",C30="MH",C30="Rem."),
OR(F30="mh-f3")
),
(H30+O30-N30-L30)+(H30+O30-N30-L30)\*0.3,
IF(
AND(
OR(C30="bss cit.",C30="cms cit."),
OR(F30="mh-f3")
),
H30+O30-N30-L30+K30,
IF(
AND(OR(C30="bss cit.",C30="cms cit."),
OR(F30="23idfna")
),
((H30+O30-N30-L30+K30)+(H30+O30-N30-L30+K30)\*0.3),
IF(
AND(OR(C30="US",C30="mh",C30="Rem."),
OR(F30="if3")
),
((H30+O30-N30-L30+K30)+(H30+O30-N30-L30+K30)\*0.3)
)
)
)
)
)
)
I've tried to attach the excel workbook and a word doc narrative for clarity. If you clink the link to Onedrive, it should bring you directly to them.