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-09T22:18:53+00:00

Hi Daryl

I was wondering what these terms mean to see if their meanings have items we can identify to maybe incorporate the relevance to the DIM table:

CMS Cit.

BSS Cit

from what I can gather, they are Citations

Also what does the term:

CVN fee

mean and are there any features related to it that can be incorporated with the DIM table such as will some products have this fee tacked on or not depending on the product type. Thank you.

cheers
Was this answer helpful?

0 comments No comments
Anonymous

2021-06-09T17:07:51+00:00

Hello Yea So,

If I understand your question regarding referencing columns being more intuitive then referencing cells, I don't think it really is, since with both formulas, the calculation still pulls the data from the corresponding cell. Perhaps I'm not seeing it from the right perspective? Nonetheless, it is interesting to see!

Regards,

Daryl
Was this answer helpful?

0 comments No comments
Anonymous

2021-06-09T13:41:41+00:00
Hi Daryl,

I'm testing the body of the form to be a table so the formulas can be written this way:

=IF(
```
OR([@CalcType]="23IDFNA",[@CalcType]="24IDFA",       [@CalcType]="IF3",[@CalcType]="ME-F3"),
```
[@BaseAmt]*0.12/365*([@GdThru]-[@RecDate])*[@[Disc%]],

[@BaseAmt]*0.18/365*([@GdThru]-[@RecDate])*[@[Disc%]])

do you think its more intuitive than referencing cells but instead referencing the column name for the calculation of that row? I'm still trying it since i've never used it before.
Was this answer helpful?

0 comments No comments
Anonymous

2021-06-09T10:22:08+00:00

Good morning Yea So,

Cool! I have never used conditional formatting - I will have to do some investigating to see how to connect the two separate (and currently totally independent) drop-down lists so that they interrelate. Do you know where in Microsoft I could go for some good tutorials? It occurs to me that I could also create one longer drop-down list that combines product with calculation types, thereby eliminating one of the columns and perhaps simplifying the VLookUp table as well.

Cheers to you!

Sincerely,

Daryl
Was this answer helpful?

0 comments No comments
Anonymous

2021-06-09T05:05:37+00:00

Hi Daryl,

I found some holes in your drop down for your product calculation, when the user selects the Citation type, in the calculation method it should only display relevant calculation methods relating to the citation type, i.e.

CMS Cit.

BSS Cit.

the user should only see:

ME-C

XH-C

MH-LCA

23IDFNA

24IDFA

selections relevant to the above selected citation type

for the F3:

US

MH

Rem.

users should only see:

ME-F3

XH-F3

MH-F3

IF3

in the calculation selection

so conditional formatting should be appropriate when there was a leftover selection to warn to change the selection in the calculation type:

if its mismatched

cheers
Was this answer helpful?

0 comments No comments