Excel IF/AND/OR formula support

[tmadaras]

I have 2 formulas that I need help with - For this first formula, I cannot get the field to give me data for all names and levels it ONLY gives me data for all levels of Bronze but not the others. 

=IF(AND($C$13="Bronze",$D$16="Level 1"),Bronze!K15,IF($D$16="Level 2",Bronze!V15,IF($D$16="Level 3",Bronze!AG15,IF($D$16="Level 4",Bronze!AR15,OR(IF(AND(C13="Silver",D16="Level 1"),Silver!K15,IF(D16="Level 2",Silver!V15,IF(D16="Level 3",Silver!AG15,IF(D16="Level 4",Silver!AR15,IF(AND(C13=”Gold”,D16-"Level 1"),Gold!K15,IF(D16="Level 2",Gold!V15,IF(D16="Level 3",Gold!AG15,IF(D16="Level 4",Gold!AR15, IF(AND(C13=”Platinum”,D16-"Level 1"),Platinum!K15,IF(D16="Level 2",Platinum!V15,IF(D16="Level 3",Platinum!AG15,IF(D16="Level 4",Platinum!AR15,)))))))))))))))))

AND # 2 - For this second formula, I get no data at all coming back -

=IF(AND(M16=TRUE,$C$13="Bronze"),Bronze!H72,IF(AND(M16=TRUE,$C$13="Silver"),Silver!H72,IF(AND(M16=TRUE,$C$13="Gold"),Gold!H72,IF(AND(M16=TRUE,$C$13="Platinum"),Platinum!H72,""))))

[#2]

@tmadaras 

First formula. Logic is not exactly clear. Your formula:

=IF(AND($C$13="Bronze",$D$16="Level 1"), Bronze!K15,
  IF( $D$16="Level 2", Bronze!V15,
  IF( $D$16="Level 3",Bronze!AG15,
  IF( $D$16="Level 4",Bronze!AR15,
       OR(
           IF( AND( C13="Silver", D16="Level 1"), Silver!K15,
           IF( D16="Level 2",Silver!V15,
           IF( D16="Level 3",Silver!AG15,
           IF( D16="Level 4",Silver!AR15,
           IF( AND(C13=”Gold”,D16-"Level 1"),Gold!K15,
           IF( D16="Level 2",Gold!V15,
           IF( D16="Level 3",Gold!AG15,
           IF( D16="Level 4",Gold!AR15,
           IF( AND(C13=”Platinum”,D16-"Level 1"),Platinum!K15,
           IF( D16="Level 2",Platinum!V15,
           IF( D16="Level 3",Platinum!AG15,
           IF( D16="Level 4",Platinum!AR15,))))))))))))
      )
))))

Perhaps you mean

=IF( $C$13="Bronze",
    IF( $D$16="Level 1", Bronze!K15,
    IF( $D$16="Level 2", Bronze!V15,
    IF( $D$16="Level 3",Bronze!AG15,
    IF( $D$16="Level 4",Bronze!AR15, "nothing")))),

  IF( C13="Silver",
  IF( D16="Level 1", Silver!K15,
  IF( D16="Level 2",Silver!V15,
  IF( D16="Level 3",Silver!AG15,
  IF( D16="Level 4",Silver!AR15,  "nothing")))),

  IF( C13="Gold",
  IF( D16="Level 1",Gold!K15,
  IF( D16="Level 2",Gold!V15,
  IF( D16="Level 3",Gold!AG15,
  IF( D16="Level 4",Gold!AR15,  "nothing")))),

  IF( C13="Platinum",
  IF( D16-"Level 1", Platinum!K15,
  IF( D16="Level 2",Platinum!V15,
  IF( D16="Level 3",Platinum!AG15,
  IF( D16="Level 4",Platinum!AR15, "nothing"))))

))))

That is here:

[#3]

@tmadaras 

Second formula - you shall to check R16, not M16:

=IF( AND(R16=TRUE,$C$13="Bronze"),Bronze!H72,
  IF( AND(R16=TRUE,$C$13="Silver"),Silver!H72,
  IF( AND(R16=TRUE,$C$13="Gold"),Gold!H72,
  IF( AND(R16=TRUE,$C$13="Platinum"),Platinum!H72,""
))))

or to simplify a bit

=IF( R16,
     IF( $C$13="Bronze",Bronze!H72,
     IF( $C$13="Silver",Silver!H72,
     IF( $C$13="Gold",Gold!H72,
     IF( $C$13="Platinum",Platinum!H72,""
)))) )

[#4]

Thank you but now it is changing every field and not individual fields - so if I change the level in D16 then all Fields Under cost are exactly the same and change together. I am trying to get it to where each field under level of effort will change the specific cost under the Cost field corresponding to the level of effort in the same row.

[#5]

@tmadaras 

Just in case, second formula could be

=IF( R16, INDIRECT( $C$13 & "!H72" ), "")

[#6]

@tmadaras 

Are you in any way open to rethinking how your spreadsheet is organized? That level of nested IFs is almost a case-study in the kind of formulas that most Excel texts advise NOT doing. Why? Well, you're experiencing the "why"--such formulas, with levels of conditionals going so deep, very quickly become unintelligible and unmanageable.

The reality is that using one of the many LOOKUP functions (VLOOKUP, XLOOKUP), or related capabilities (such as INDEX and MATCH) or even the more recent FILTER--using one of those with a well designed table could serve the same purpose while also being far more maintainable, far more flexible, far more--well, a whole host of positives.

It would require re-organizing the data on those bronze, silver, gold and platinum into a single table, and that would be some work, but the result would be far more accessible to Excel. You've clearly created those sheets for human consumption, but in the process have gotten in the way of using Excel cleanly.

Hence the question: are you open to re-thinking your approach?

[#7]

@mathetes - I am definitely open to rethinking the spreadsheet - I am a novice at Excel and I am learning. I do like the suggestion - do you have an example of what you are suggesting I could look at and I will take a stab at it. Thank you.

[#8]

@tmadaras 

I changed formula only in this very cell

and didn't change the rest. That's only reverse engineering of your initial formula, have no idea what is the business logic behind.

[#9]

@tmadaras 

I've attached an example created simply as a demonstration--it does use the LET function which is very new, so you'll need to have the most recent version of Excel to make it work. If it doesn't work for you, please let me know and I'll find another example.

This example calculates US income tax, and is somewhat comparable to your situation in that I actually have multiple tables underneath the central formula--one table for each of the different tax filing statuses possible--and all it is doing is a single calculation based on a single input, but it uses the VLOOKUP function to access the appropriate row of the appropriate table.

Beyond that, or maybe BEFORE that, since you are a beginner in Excel, it might be helpful if first you just look at a basic primer on tables and VLOOKUP. Here are some links that could be helpful in that foundational learning: 

Tables: https://exceljet.net/excel-tables

VLOOKUP: https://exceljet.net/excel-functions/excel-vlookup-function