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. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-06-04T21:51:13+00:00

    Hi again Daryl,

    I was able to test the formula in my previous post by creating some dummy data. However, to assist you further, Can you please upload an example workbook with the DropDowns.

    In a couple of rows make some selections that should match and return the calculation and and a couple of rows that should NOT match and therefore return a blank. Highlight the rows with a color that should match to return the the calculation.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 7 to get the link but please zip the file before uploading.)

    Sharing links to business OneDrive often does not work because the business has applied security measures that prevent this. Some people take a copy of the workbook home and upload from their private OneDrive.

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file. (Do not use 3rd party compression applications because I cannot unzip them).
    3. Go to this link. https://onedrive.live.com
    4. Use the same login Id and Password that you use for this forum.
    5. Select Upload under the blue bar across the top and browse to the zipped folder to be uploaded.
    6. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    7. Right click the file name in OneDrive.
    8. Select Share.
    9. Click the link icon (Looks like chain links) at the bottom left of the dialog (Just above "Copy link").
    10. Click Copy button.
    11. Change back to this forum and click the "Insert Hyperlink" icon at top of the posting editor (Icon looks like chain links).
    12. Right click in the Web address field and right click and paste (or just Ctrl V to paste).
    13. Click "Insert" Button.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-06-04T14:52:21+00:00

    Hi Tin,

    Just one so far; see below reply from Ossie and my failed attempt to successfully implement his suggestions.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-06-04T14:42:37+00:00

    Hi Ossie,

    Thanks for your reply! I tried modifying my nested formula based on what you said, but Excel doesn't like it. This was the old nested formula for settlements; below that is how I changed it to use your suggestions.

    =IF(OR(C30="US",C30="mh",C30="Rem.",C30="bss cit.",C30="cms cit.",AND(F30="me-c",F30="me-f3",F30="xh-c",F30="xh-f3")),

    ((H30-I30+J30)+(H30-I30+J30)*0.3),

    IF(OR(C30="CMS Cit.",C30="BSS Cit.",AND(F30="mh-LCA")),

    (H30*0.3)+H30,

    IF(OR(C30="US",C30="MH",C30="Rem.",AND(F30="mh-f3")),

    (H30+O30-N30-L30)+(H30+O30-N30-L30)*0.3,

    IF(OR(C30="bss cit.",C30="cms cit.",AND(F30="23idfna")),

    H30+O30-N30-L30+K30,

    IF(OR(C30="bss cit.",C30="cms cit.",AND(F30="24idfa")),

    ((H30+O30-N30-L30+K30)+(H30+O30-N30-L30+K30)*0.3),

    IF(OR(C30="US",C30="mh",C30="Rem.",AND(F30="if3")),

    ((H30+O30-N30-L30+K30)+(H30+O30-N30-L30+K30)*0.3)))))))

    Following is how I modified it:

    =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."),(F30="mh-LCA")),

    (H30*0.3)+H30,

    IF(and(OR(C30="US",C30="MH",C30="Rem."),(F30="mh-f3")),

    (H30+O30-N30-L30)+(H30+O30-N30-L30)*0.3,

    IF(and(OR(C30="bss cit.",C30="cms cit."),(F30="23idfna")),

    H30+O30-N30-L30+K30,

    IF(and(OR(C30="bss cit.",C30="cms cit."),(F30="24idfa")),

    (H30+O30-N30-L30+K30)+(H30+O30-N30-L30+K30)*0.3,

    IF(and(OR(C30="US",C30="mh",C30="Rem."),(F30="if3")),

    if(and(or(H30+O30-N30-L30+K30)+(H30+O30-N30-L30+K30)*0.3,"")))))))))

    Unfortunately, Excel didn't like it and gave the standard error message, "There's a problem with this formula. ... use apostrophe ' if not a formula... "

    Any suggestions?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-06-04T09:26:32+00:00

    Hi，

    May I know whether there are any updates to your question? If you have any questions, please feel free to contact us.

    Tin

    Was this answer helpful?

    0 comments
  5. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-06-03T07:18:12+00:00

    Hi Daryl,

    Try the following.

    My understanding of your question is IF C30="CMS Cit." or C30="BSS Cit." plus  F30="mh-LCA"

    If above is correct then try the following formula. It will return blank if no match.

    =IF(AND(OR(C30="CMS Cit.",C30="BSS Cit."),(F30="mh-LCA")),(H30*0.3)+H30,"")

    The OR part of the formula is one part of the AND and the other part of the AND is F30="mh-LCA"

    Was this answer helpful?

    0 comments