Multiple Option IF formula

    I need a formula that will calculate if a Category is at Very High, High, Med, or Low level risk. I've attached a sample file for review. In the sample I use a countif and countblank formula to count the number of specific statuses used. On my summary page I want to use the counts to determine if a category is in one of the 4 risk levels. I've broken down additional information within the attached file.
    Thanks in advanced for any help.

    Re: Can a Multiple option IF formula be created?


    Quote from ByTheCringe2

    Just a comment. You will need to use VBA for the colours, because you can only have three in conditional formatting, and you want four.


    Not really, four colours is fine you have all cells formated to the base colour (Low) the use the 3 conditional formats for Med, high and V high.


    so no need for VBA in this case.

    Re: Can a Multiple option IF formula be created?


    Quote from ByTheCringe2

    DOC, Have you seen the attachment? He has white base, plus possible red, blue, green, yellow.


    No champ i am running an ap and cant open it but if there are only 4 possible outcomes / colours then the white wont be needed ( might be wrong ) and appolgies if so.

    Re: Can a Multiple option IF formula be created?


    Quote from DOC

    if there are only 4 possible outcomes / colours then the white wont be needed ( might be wrong ) and appolgies if so.

    There are several cases where none of the risks applies, so I think I'm right. If so, it's best to do the whole think in VBA, I presume. (Don't know quite what is supposed to happen to those cases though...)

    Re: Can a Multiple option IF formula be created?


    ByTheCringe2 is see what you mean, in that sample using only three colors would work perfectly fine, my trouble is formulating a way to determine if a vaule has no 4, and 3 statuses its considered a Very High or High Risk.

    Re: Can a Multiple option IF formula be created?


    DOH!!!! Well done, Jim, while I'm faffing on about too many colours, you've done it. What I failed to realise is that there is only one conditional format needed in any one cell, because they are in different columns. I must wake up...

    Re: Can a Multiple option IF formula be created?


    slick225, when you start a New Thread it is clearly written that Thread Title should not use superflurous words like "can a" etc. Please take more care and think of you fellow forum members who may search.

    Re: Multiple option IF formula


    To everyone that participated on this thread, thank you for all your brain power and hard work.

    I just wanted to update everyone on my task.

    I decided to go a completely different route: The goal was originally to report on our teams knowledge levels of products and based on how many Expert users, Well versed users, and no experienced users we had, to display weather it was a High, Medium, or Low risk area.

    My approach: (using a combination of IF, OR, & ANDs)
    I first did a count of all types, then using the counts did the following:
    =IF(OR(And(scenario 1)),And(scenario 2), "High Risk", "")

    This actually got me the exact results I was looking for, I was able to use various scenarios to decide my High, Medium, and Low risk categories.

    Scenarios looked like this: A2=>3, B2=<4, etc.

    By the way, my apologies for the "Can a" in the Tread Heading.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login