Multiple IF Statements On Y/N Cells

[Syndrome78]

I have a new rate that I have to add for a calculation spreadsheet, but I can't figure out how to integrate it using existing IF Statement formulas.

I have 4 rates for a class; rate 1 is a standard rate, rate 2 is a discounted rate for primary service, rate 3 is a discounted rate for primary service and transformer ownership, and rate 4 (new one) is for transmission level service.

There are now 3 trigger cells where you can choose Y/N, previously there were only 2. They are Primary Feed, Own Transformer and now Transmission Feed. The previous formula was =IF(Primary Feed="y",IF(AND(Primary Feed="y",Own Transformer="y"),Rate 3,Rate 2),Rate 1)

I need to add the third Y/N trigger to the formula, but can't figure it out how cause some one using the spreadsheet might put Y for all 3 triggers and get and incorrect rate calculation.

 

[lionelhill]

Your existing "if" is already unnecessarily complicated and doesn't need the "AND". The reason is that PrimaryFeed must be "Y" for Excel to be evaluating the 2nd parameter of the first "IF", so you don't need to include it in the AND.

There are various ways to do what you want, but the first thing you need to do is establish a logic-grid of what rates correspond to what combinations of "Y"'s in the three trigger cells. Some combinations might not be possible.

If the situation is simple, you can combine them in one happy IF. For example:
=if(tick1="y", if(tick2="y", if(tick3="y", rate4, rate3), rate2), rate1)
deals with the simple situation of each additional tick adding an additional service-level.

Personally, I would do something different. I would concatenate all my answers to give something like "ynn" (formula is =A1&B1&C1), and then have a look-up table containing (column A) all combinations "nnn" to "yyy", and (column B) a list of corresponding rates. Name this range as "MyTable"
Then the formula would be something like
=vlookup(A1, MyTable, 2, false).

 

[mintjulep]

You could simplify things greatly if you used Boolean TRUE and FALSE instead of y and n. Then there would be no need to compare the contents of the cells to a string, just work directly with the values.

 

[combo]

You could combine boolean answers (as mintjulep suggests) with binary system and INDEX function to search table:

=INDEX(MyTable,1+1*A1+2*B1+4*C1)

Excel calculates TRUE=1, FALSE=0.

combo