Forum Discussion

JanStewart's avatar
JanStewart
Copper Contributor
Oct 08, 2020

Compound If statements

I'm trying to create an excel spreadsheet that will calculate price depending on a) number of transactions and b) by Channel Partner.  The IF statement is unruly and we are planning on adding more Channel Partners.

 

How can I make this easier?

 

Here is an example of the IF statement

=IF($E$21>0,IF($B$4="BMO",IF($B$18="Basic",IF($E$21>='Master Data'!$F$182,'Master Data'!$I$182,IF($E$21>='Master Data'!$F$181,'Master Data'!$I$181,IF($E$21>='Master Data'!$F$180,'Master Data'!$I$180,'Master Data'!$I$179))),IF($B$18="Complete",IF($E$21>='Master Data'!$F$189,'Master Data'!$I$189,IF($E$21>='Master Data'!$F$188,'Master Data'!$I$188,IF($E$21>='Master Data'!$F$187,'Master Data'!$I$187,IF($E$21>='Master Data'!$F$186,'Master Data'!$I$186,IF($E$21>='Master Data'!$F$185,'Master Data'!$I$185,IF($E$21>='Master Data'!$F$184,'Master Data'!$I$184,'Master Data'!$I$183)))))),0))))

 

An example of the for cell J21 in spread below

excel

19 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 08, 2020

    JanStewart 

    Life would become far easier if you could set your Master Data up so that it is searchable rather than hard-wiring the links between sheets manually.  For example

     

    = LOOKUP( 1, 
    1 /(Packages[CP Code]=Partner)
      /(ISNUMBER(SEARCH(Level, Packages[CP Packages]))),
    Packages[AW Price] )

     

    You will also need to include a test on 'Setup', 'Maintenance' etc.

    • JanStewart's avatar
      JanStewart
      Copper Contributor
      Oct 09, 2020

      PeterBartholomew1 Thank you for your reply.. are you saying my Master Data is not setup efficiently?  Most of my cells are dependent on 2 - 3 criteria.  It would be much appreciated if you could provide the LOOKUP formula for cell G18 & G21 so I may use it as an example.  Your help is much appreciated.  Thank you.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 09, 2020

        JanStewart 

        I'd do not rely on direct lookups since texts to lookup could vary (e.g. Package (C) = PACK C, etc.). Perhaps parallelly to data validation lists

        create one more range which maps above on position of cell to pick-up. Or even better to link here cells from which values ae to be taken, with that we are less dependent from insertion/deleting rows in main list.

         

        But it very depends on how information about the packages is updated - is it fixed structure or not, does text in CP Packages are exactly the same all the time or they could vary (Package(C) = PACK C =

         PACKAGE C = pkg C = etc).

         

        Other words, it's not enough knowledge of business logic on our site (which could be obvious for you). 

Resources