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
2021-06-03T03:50:40+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

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

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-06-08T21:36:33+00:00

    Hi Daryl,

    The advantage of using a table is to simplify the logic of your formulas so that each field will just have one generic formula i.e VLOOKUP this value in that table and whatever the calculation for that combination of values give me the cacluated result in column x in that table. Instead of embedding the logic of the table in each formula in each cell of the form. For example, lets say a week from the day you finished your form, your superior says oh we have a new product with new calculation, Daryl can you incorporate that new product calculation into the form you invented? You'll be scratching your head and start sweating profusely because now you have to figure out how to incorporate that new product into each formula in each cell and make it work. On the other hand if you used the table to simplify your logic having one generic formula on each cell looking for calculation variables, you can easily add that new product calculation in the table and since the table is defined as a table in excel it is dynamic to changes in the range to your generic VLOOKUP range in the cells of your form, and all you have to do is add the product calculation to the table. The maintainability of such a solution makes your job "easy" plus you get to impress your superior that you did it in such a short amount of time "kissing points from your superior" haha.

    cheers

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-06-08T10:27:03+00:00

    Wow- it’s a plan. I used to do v look up calls every month for reports that were required and became fluent in it. That was about 12 years ago, so I will have to re-learn that method. Once the table is made though, it sounds like a good solution.

    If you are familiar with the newer IFS function that’s part of Office 365, do you have any thoughts on using it as a simple solution? Just wondering….

    Cheers!

    Daryl

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-06-08T10:09:35+00:00

    Good morning,

    Last night I uploaded to OneDrive and provided a link in my reply to Ossie.. Providing you can look at the workbook and Word narrative, I have written out the formula for each of the calculation methods.

    Cheers!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-06-08T05:31:52+00:00

    Hi Daryl,

    So for simplicity's sake, there are three selections that need to be paired with five selections, so 5x3=15.

    so there are 15 combinations of selection between the DD1 and DD3 (DD being the Drop Down)

    So create a table of those 15 pairings and show which of the 9 calculations belong to any of the 15 pairings so we can create a look up table that will always have already calculated the values of the 15 pairings, and our formula will just involve doing a VLOOKUP to specific combinations for example

    CMS Cit. and LFV

    which calculation should be used, we can have the cell already calculated and as soon as that selection has been made, in the look up table the two selections are concatenated as in: CMS Cit, LFV in one cell and the calculation in the adjacent cell so we can just have the calculation in the IF statement say

    VLOOKUP(TEXTJOIN(",",FALSE,[DropDown1],[DropDown2]), [LookUpTableRange],[Column2],[FALSE]))

    And the Calculation on the 2nd column of the lookup table will be returned in the Answer Cell in the Recap Surplus Hrdshp Stlmnt tab. So no mor if then else and or confusion but a simple vlookup

    cheers

    Was this answer helpful?

    0 comments
  5. 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