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-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 No comments
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 No comments
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 No comments
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 No comments
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 No comments