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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2021-06-05T07:47:23+00:00

    Hi Daryl,

    You cannot expect other people to analyze your issue under the basis of your failed formula. You must provide several information:

    Given:

    You have five product items:

    5 items in it that the user can select,

    You have nine calculation types:

    drop-down list of 9 items for the user to select.

    You have specific math calculation associated with certain combination of Product + Calculation type:

    specific math function.

    If you think that we are going to help you solve your formula issue by guessing what those five product types, and nine calculation types, plus guess 9x5=45 calculation permutations by just showing us your failed formula, I would highly recommend you share that workbook, or a version of that workbook, because having to guess what the five products and the nine calculation types is already pushing the envelope for a solution, never mind the 45 combination of math calculation that we also have to guess.

    cheers

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-06-05T14:29:31+00:00

    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

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-06-07T03:08:43+00:00

    Hi Daryl,

    I got curious about your formula so I investigated it, and have a question:

    How can cell F30 have all these values all at once?:

    AND(F30="me-c",F30="me-f3",F30="xh-c",F30="xh-f3")),

    just wondering since you said you had two drop down so it would probably be safe to assume that the first drop down is at cell C30, and the other drop down is at cell F30.

    so after the user makes their selection at the C30 drop down, then finishes making their selection at the F30 drop down, how is it possible that F30 would have all these values all at once with one selection by the user, because it somehow conflicts with the AND statement in the formula. So is it an AND? or is it an OR?

    cheers

    here is a copy of your formula

    dissected:

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-06-07T23:35:07+00:00

    Hi!

    The column for products and the column for calculation types each have a drop-down list for the user to select. The user can select a citation type of product and match it with any one of three or even four types of calculation methods, depending on the circumstance. Depending on the combination, the calculation method is determined.

    I am currently working at uploading to Onedrive and linking it to Forum the workbook with a narrative of my comments for clarity.

    Regards,

    Daryl

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-06-08T00:36:58+00:00

    Was this answer helpful?

    0 comments