Dec 30 2020 08:59 AM - edited Dec 30 2020 09:08 AM
Hi Community!
I need help on a nested IF AND formula. I have the first part correct with =IF(AND(B17=A1,B18=D2,B19="Monthly"),B3) where I have a separate table with values in A1, B2 & B3. I have several more values in the table in different cells.
What I want to do is combine several IF AND formulas into one cell so that when I change the values of cells B17, B18 & B19, the cell with the formula (D19) displays an answer from the table. If there is a better way to set this up than using nested IF AND, then please advise.
Thanks and Happy New Year!
Dec 30 2020 10:42 AM
SolutionTo make your NESTED IF statement less complicated and more dynamic, it is best to unpivot your data and maintain one single table reference (as long as the same parameters/information are shared). Here's an example of what I mean:
With this new setup, your complex NESTED IF statement is no longer needed and a simple SUMIFS statement can be used to return the right Fee figure you're looking for
Dec 30 2020 11:11 AM
@adversi Excellent! Thank you so much. I'll work with this and post again if I have any problems.
Much appreciated!
Dec 30 2020 01:46 PM - edited Dec 30 2020 02:09 PM
Normalising the data into a table has a lot to commend it, but it is possible to work directly with data presented as arrays.
= LET(
FeeTable, IF(Binsize=35, Size35fees, Size64fees),
Freq, XMATCH(Frequency, FrequencyOptions),
Fee, INDEX(FeeTable, Freq, Quantity),
Fee)
or, in older versions of Excel
= INDEX(
IF(Binsize=35, Size35fees, Size64fees),
MATCH(Frequency, FrequencyOptions, 0),
Quantity)
Dec 31 2020 09:05 AM
@Peter Bartholomew Thanks, Peter. I like the drop down menu feature. I'll play around with both and come up with something that works well for our purposes.
Much appreciated and have a great New Year.
-Nick
Jan 02 2021 03:59 AM
Jan 04 2021 04:49 AM
@Sergei Baklan Thank you! I like this because it allows me to keep the table in matrix form.
Much appreciated!
Nick
Dec 30 2020 10:42 AM
SolutionTo make your NESTED IF statement less complicated and more dynamic, it is best to unpivot your data and maintain one single table reference (as long as the same parameters/information are shared). Here's an example of what I mean:
With this new setup, your complex NESTED IF statement is no longer needed and a simple SUMIFS statement can be used to return the right Fee figure you're looking for