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

Anonymous

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

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.

0 comments

41 answers

Anonymous

2021-06-08T05:10:36+00:00

Hi Daryl,

So what are the sets of calculation permutation for the selections

cheers
Was this answer helpful?

0 comments No comments
Anonymous

2021-06-08T04:30:17+00:00

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
Was this answer helpful?

0 comments No comments
Anonymous

2021-06-08T04:26:07+00:00

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
Was this answer helpful?

0 comments No comments
Anonymous

2021-06-08T04:03:23+00:00
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
Was this answer helpful?

0 comments No comments

Anonymous

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) 

              ) 

           ) 

        ) 

     ) 

  )

)

0 comments