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-09T02:44:14+00:00

Hi,

Thanks for illustrating the advantages of updating in a straightforward fashion when changes are needed, as inevitably they eventually will be. You make a compelling argument for vlookup and not using the IFS function. I imagine that a table would be easier to see and not miss any permutations, as you may miss by implementing IFS with its listing methdoogy mI will re-visit some tutorials about creating echanics. Over the next week or so I will revisit vlookup tables and how to create and implement them.

Well, Good night! 😴 I think you and I are on opposite sides of the world, guessing by when you appear active for my time zone.

Thanks for your help, as always!

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