Logic type table help

Gnalen

Can anybody suggest a way to do this. I have it working in excel, I can't seem to get it working.

I have this excel formula that works.

=IF(AND(A2="OT", B2="UNDER"), "LOW",
    IF(AND(A2="OT", B2="OVER"), "MEDIUM",
        IF(AND(A2="LATE", B2="UNDER"), "MEDIUM",
            IF(AND(A2="LATE", B2="OVER"), "HIGH", "")
        )
    )
)

I want to compare the status and budget columns and display the risk.

Thanks for any help.

Gnalen

Other than some error or blank formatting I was able to with it this way.

=IF(AND([On-Time Status]@row = "OT", [Budget Variance]@row < 0), "Low", IF(AND([On-Time Status]@row = "OT", [Budget Variance]@row > 0), "Medium", IF(AND([On-Time Status]@row = "Late", [Budget Variance]@row < 0), "Medium", "High")))

Is the the best way?

Gnalen

My final formula with some cleanup

 =IF(ISBLANK([On-Time Status]@row), "", IF(AND([On-Time Status]@row = "OT", [Budget Variance]@row >= 0), "Low", IF(AND([On-Time Status]@row = "OT", [Budget Variance]@row < 0), "Medium", IF(AND([On-Time Status]@row = "Late", [Budget Variance]@row >= 0), "Medium", IF(AND([On-Time Status]@row = "Late", [Budget Variance]@row > 0), "Low", "High")))))

ON-TIME and On Budget = Low

ON-TIME and Over Budget = Medium

Late and On Budget = Medium

Late and Over Budget = High

was there an easier way?

Thanks Gary

KPH

Hi Gary

The formula looks good and matches how you built it in Excel.

Your second cleaned up version will return different answers to the first because you are swapping the under and over budget around.

I'm not sure if less than 0 means under budget or over budget. But that is a small thing to fix. You could simplify the formula, if you wanted to, and I will explain how using the second formula.

As the IF function stops at the first true thing, you don't need to repeat things. Here is a shorter formula to do the same thing:

=IF(ISBLANK([On-Time Status]@row), "", IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))

Here is how it works:

This first IF (shown in bold, below) returns blank if isblank is true, exactly as you had it.

=IF(ISBLANK([On-Time Status]@row), "", IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))

If isblank is not true then the second IF (shown in bold, below) is evaluated, here we just check the On-Time Status (no need for the AND).

=IF(ISBLANK([On-Time Status]@row), "", IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))

If On-Time Status is OT then the third IF (shown in bold, below) returns "Low" if Budget Variance is >= 0 and "Medium" if not. And you close that IF (both the true and false outcomes are complete).

=IF(ISBLANK([On-Time Status]@row), "", IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))

The next IF is in the false position for the second IF, so it is only evaluated if On-Time Status is not OT. You don't need to specify what this is as you only have two options - if it is not OT, it must be Late (as you have already rule out blanks with the first IF). If you had a third value in this column, you would need another IF. But as you don't all you need to look it is the Budget Variance. So the fourth IF does that.

=IF(ISBLANK([On-Time Status]@row), "", IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))

Hope that all makes sense. Let us know if you have any questions. If you want to swap the under/over budget just change the direction of the greater than/less than signs, or switch the "low" and "medium" and "medium" and "high" with each other.

Gnalen

KPH,

Thanks you for the detailed explanation on how the nesting works. I have not decided if over budget will be a positive or negative number, so it will be easy to change the sign. I trying to digest the nesting method

I though Ii needed to have both conditions to satisfy the the requirement: OT and >= 0 to be low, that is why I used "AND"

When I look at the simplified formula "IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))"

IF([On-Time Status]@row = "OT" = True it then evaluates: IF([Budget Variance]@row >= 0, "Low", "Medium")

if it is >= 0 then I get a True, so I have a "T" "T" and "Low" is returned.

if its not >= 0 then I get a False, so I have a "T" "F" "Medium" is returned.

What happens when "OT" is not there and is "Late"

IF([On-Time Status]@row = "OT" = False it then evaluates: IF([Budget Variance]@row >= 0, "Medium", "High"

Just trying to understand where the true's and falses are and how they a passed forward.

Thanks for all your help.

-- Gary

KPH

Hi Gary

You are replacing the true with another formula. How about thinking about it this way.

Start with one basic IF:

= IF([On-Time Status]@row = "OT", "true","false")

Pop that in your sheet and see the result. You will see true or false based on the Status column.

Another basic IF:

= IF([Budget Variance]@row >= 0, "Low", "Medium")

Do this, and see that it does - just to check it is doing what you think it will.

Now, what we do is take the first formula and instead of having it return "true" when the logic evaluates to true, we have it evaluate another formula. This formula would only be evaluated if the first is true as it is in the true position. To do that you paste the other formula over the word "true". You need to remove the quotation marks as well.

This

= IF([On-Time Status]@row = "OT", "true","false")

Becomes this

= IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"),"false")

Do this in your sheet and look at the results. Now you will see 3 outcomes, Low, Medium, (which appear if Status is OT) and false (which appears if Status is not OT).

So next, we replace the "false" with another formula. This formula will only be evaluated on the rows that were returning "false".

= IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High"))

In your formula you then pop that formula into the false position of an IF(ISBLANK

Does that help?

Gnalen

That helps,

Is this the same in Excel? I think I want to take some classes so I can be more efficient in this stuff.

Thanks for all your help.

-- Gary

Hans1958

Dear ladies and gentlemen,

My approach is completely different, because I am not very fond of this endless boxed up IF's.

My solution is working with VLOOKUP and some "Helper" columns.

a) In the rows 1 through 5 (... and of course lots more beneath) you'll have your cases.

b) There is a VALUE LIST from row 9 through 12, as well a RISK VALUE List from row 15 through 38.

c) The advantage of this Lists is, that you can assign yourself the importance, the values, that you consider e.g. on-time, late, under... over budget, etc.

d) The grey highlighted columns "Status Value" and "Budget Value" pulls the values by VLOOKUP.

Row1 at "Status Value": =VLOOKUP(Status@row, $Tables$9:$Value$12, 2, false)

Row1 at "Budget Value": =VLOOKUP(Budget@row, $Tables$9:$Value$12, 2, false)

I think I don't need to explain how VLOOKUP works or why are the $-signs there in the formula. If you don't know, please feel free and ask.

e) As you may notice, at row 5 is at column "Risk Assessment" this failure code of #NO MATCH. Of course there is, because there are no data at "Status" or "Budget". Can be fixed with a IF-boxing of the formula(s) mentioned at "d".

f) Usually I would do the LISTS contain in a seperate sheet, that would be cleaner. But if it doesn't disturb anybody it can also be contained in the same sheet like here.

g) It depends of the point of view, but if somebody would like to smooth the appearance, then you can hide the Helper columns "Tables", "Value", "Status Value", "Budget Value" and "Risk Value".

Well, that is my contribution to this question.

I think this solution has two advantages:

1) You can fine-tune the assessment of the risks (e.g. row15 ... = Super Low", ... row 22 through 24 = "Super High"

2) At least in my humble opinion I think this all is more transparent.

That's it! Hope I could help! 🙂

Best regards

Hans P. ... from Germany

www.hp-u.de

Gnalen

Thank you, Hans,

I will certainly look at this method. I am just getting used to having helper sheets or columns to accomplish these types to calculations.

I will recreate it today using this method and see how it works.

-- Gary

Gnalen

Hello KPH,

https://community.smartsheet.com/discussion/comment/432205#Comment_432205

While I am investigating the vlookup, I had one more question. I need to add an additional IF, to look at another column and if there is a red in there put a High" this column takes priority over the rest?

It would almost take place of the is blank, but then I could not deal with that condition.

Thanks Gary

KPH

Hi Gary

To answer to your earlier question; yes the IF syntax in smartsheet is just like excel. Many of the formulas are the same, you just need to adjust the references.

For your second question, it might be easier to think about it the other way around. Rather than thinking you have a formula and want to put in another condition, think about what the highest priority condition is, write than formula, and then slot in the one you already had. With practice you will be able to do this automatically but as you get started you might find it easier to start with one (the left hand one) and build it up bit by bit. You can also then test each part to be sure you have the correct commas and parentheses. You can also test with other words rather than the desired outcome (which will show which of the "High"s or "Medium"s are being returned now that you have a couple).

Your first priority is to return blank for blank statuses so start with this:

=IF(ISBLANK([On-Time Status]@row), "","not blank").

Next you want to return "High" for red in another column (lacking a better term, I will call that column "color column"). You put that formula in place of the "not blank":

=IF(ISBLANK([On-Time Status]@row), "",IF([color column]@row="Red","High","Not blank and not red"))

Then you have this formula already for everything else:

=IF(ISBLANK([On-Time Status]@row), "", IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))

You just pop that in, in place of the "Not blank and not red":

=IF(ISBLANK([On-Time Status]@row), "",IF([color column]@row="Red","High",IF(ISBLANK([On-Time Status]@row), "", IF([On-Time Status]@row = "OT", IF([Budget Variance]@row >= 0, "Low", "Medium"), IF([Budget Variance]@row >= 0, "Medium", "High")))))

Gnalen

Now that approach makes sense.

Thank you for that, it is really helping me complete my first dashboard.

-- Gary

KPH

No problem at all. Good luck with that dashboard.

Gnalen

Thank you all for the help with my first dashboard. It looks good now and I will use what I learned to make it even better.