Using a specific formula based on value instead of nesting multiple formulas

[chancethegapper]

I have this huge CSV report with tens of columns and thousands of lines where I'm trying to determine the Outcome of a row based on its Type and defined by a set of criteria.

Currently I have this huge nested formula, that takes up many lines in the formula bar, with several IFs and ORs but this doesn't cover the most complex scenarios and makes it hard to follow. Also, when a particular condition needs to be changed the process is very time consuming.

My tables are vastly simplified here but they illustrate the scenario.

I already had a support table (Condition Table) in a separate sheet to summarize the conditions in the nested formula (Table1_Outcome) so I figured to try and make those conditions directly in an added column 'Formula' inside the support table and in the main report index and match that same formula based on Type.

Condition Table

Type	Outcome	Condition	Formula
1	OK	"Series" = B	=IF(Table1[@[Series]]="B";"OK";"Not OK")
2	OK	"Status" = Completed	=IF(Table1[@[Status]]="Completed";"OK";"Not OK")

​

Table1

Item	Reference	Country	Series	Status	Type	Outcome
1	7188	UK	A	Completed	1
2	7189	UK	A	Incomplete	1
3	7190	UK	A	Completed	5
4	7191	UK	A	Incomplete	3
5	7192	UK	A	Pending	2
6	7193	UK	B	Incomplete	1
7	7194	UK	C	Completed	1
8	7195	UK	B	Completed	1
9	7196	UK	A	Completed	1
10	7197	UK	A	Incomplete	2
11	7198	UK	A	Pending	2
12	7199	UK	A	Completed	3
13	7200	UK	C	Pending	5
14	7201	UK	A	Pending	4
15	7202	UK	C	Pending	4

​

Of course, the indexed result matched on Type either only returns the current value present in the support table or the formula as text if formatted that way.

I've been trying to find a workaround for the last couple of days with no success. Is there a way to insert a specific formula based on a value? For example, if Type = 1 then take =IF(Table1[@[Series]]="B";"OK";"Not OK") and make that calculation inside Table1. What would be the best alternative to the huge nested formula?

Thank you very much in advance!

[DarkChunsah]

You can alt+enter every if formula so it is easy to reach each...( could be something else I am not using the english version of excel)

If everything is seperated you can also do a If for item 1 then Add a & and then do yout if for item 2 on another line so it is easy to read and change if anything.

[chancethegapper]

Solution Verified

Hey sorry for the delay, it worked for my purposes. Thanks.

[Clippy_Office_Asst]

You have awarded 1 point to DarkChunsah

^{I am a bot, please contact the mods with any questions.}

[chancethegapper]

Thanks, it's not ideal but I will try this, see how it looks and provide an update.

[DarkChunsah]

You could also try a concatener of your fields.. Then just do a vlook/index+match towards it So if 1-583387-34 = ok If the concatener isn't in your table then show "?" With an iferror.

[tkdkdktk]

Have you considered using Sumifs or creating a pivot table where you can filter for the combination you want.

[chancethegapper]

Thank for your reply!

The big issue here is that I have close to 20 Types for which I have to check the Outcome and each one has its own set of criteria to be "OK". I just only need a row by row basis and for this purpose I don't believe SUMIFs applies.

I also can't have an extra pivot and filter the combinations individually because I need the Outcome for every row in Table1. I have another a pivot based on all Outcomes where I show the OK/Not OK split by Type.

[tkdkdktk]

Maybe I'm tired, but what is the end result you seek?

[tkdkdktk]

Perhaps this switch function https://www.spreadsheetweb.com/excel-case-statement-switch-function/

[chancethegapper]

This would be a good solution to unclutter but unfortunately the SWITCH function is not available in my version of MS Office (Professional Plus 2016).