+ Reply to Thread
Results 1 to 69 of 69

How to use Multiple conditions in one cell in a dashboard

  1. #1
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    How to use Multiple conditions in one cell in a dashboard

    Hello Experts,

    This may take some of your time as it has taken quite a bit of my time with no result. Which is why i post this question here.

    I am working on a Dashboard (attached) that displays the status of employees who have finished it.

    Excel has 2 Tabs


    1. Dash Board.
    2. Look up tables.

    1. Dashboard
    we have Employee and Their Year and whether Employee has passed the course.

    I am trying to build a Dashboard functionality in 'Status'.

    Criteria is:

    For START, SPEED, LAUNCH:
    IF employee falls under Year '2014 START', '2014 SPEED', '2014 LAUNCH', '2015 START', '2015 SPEED', '2015 LAUNCH', then check for Look up 'table A'
    If Employee does not have any mandatory or Optional Certificates, then Write 'N/A'
    If employee has Passed All Mandatory Certificates (from Look up table A) + passed one of the optional Certificates then Write 'OK'.
    If employee has not passed All Mandatory certificates + not passed one of the optional Certificates then Write 'Not OK'.

    If Employee Falls Under Year '2016 START', '2016 SPEED', '2016 LAUNCH' then we need to calculate differently.
    If Employee does not have any mandatory or Optional Certificates, then Write 'N/A'.
    If employee has Passed All Mandatory Certificates from Look up table A + Secures the Minimum points required from Look up Table B then Write 'OK.
    If employee has not Passed All Mandatory Certificates from Look up table A + does not Secure the Minimum points required from Look up Table B then Write 'Not OK.
    (Course Vs Points is listed in Look up table c)

    For 'START & SPEED Completed':
    IF 'START & SPEED' are OK then write 'OK' If not 'Not OK'.
    If nothing is there, then write 'N/A'

    For Course completed
    IF 'START & SPEED & Launch' are 'OK' then write 'OK' If not 'Not OK'.
    If nothing is there, then write 'N/A'

    Thanks and Regards,
    Dhinesh.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello dhineshreddy and Welcome to Excel Forum.
    To aid the volunteers whom may attempt to assist you, it will be helpful to know the outcomes that you would expect from formulas or VBA code. To that end I have manually input, as best I understand, the values that I would expect to see. Please check those and let us know about any corrections that may be needed.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hi My friend... My Reply Yesterday appears invisible... may be I have not submitted the reply!!

    I think what you said makes sense... it is indeed bit embarrassing to put the whole question in the forum... I m working ona quite a dashboard it is the complex component of it.
    And I am exhausted with my skills to make it work in the way I want. hence is the question... If you find this question to be vauge, I surely understand it...

    I have changed the file little bit and under status I have put the expected values in RED. I want those values dynamically calculated.

    We can change the look up tables or add anything to it to get the feasible results.


    Excel has 2 Tabs


    1. Dash Board.
    2. Look up tables.

    1. Dashboard
    we have Employee and Their current Year and whether Employee has passed the course.

    I am trying to build a Dashboard functionality in 'Status'.

    Criteria is:

    START, SPEED, LAUNCH are the Phases whose Master data (constant data) is maintained in Look up tables.

    IF employee falls under Year '2014 IT', '2014 HR', '2014 FINANCE', '2015 IT', '2015 HR', '2015 FINANCE', then check for Look up 'table A' using Phases.
    If Employee does not have any mandatory or Optional Certificates, then Write 'N/A'
    If employee has Passed All Mandatory Certificates (from Look up table A) + passed one of the optional Certificates then Write 'OK'.
    If employee has not passed All Mandatory certificates + not passed one of the optional Certificates then Write 'Not OK'.


    If Employee Falls Under Year '2016 IT', '2016 HR', '2016 FINANCE' then we need to calculate differently.
    If there are no mandatory or Optional Certificates in Look up table, then Write 'N/A'.
    If employee has Passed All Mandatory Certificates from Look up table A using phases + Secures the Minimum points required from Look up Table B then Write 'OK.
    If employee has not Passed All Mandatory Certificates from Look up table A + does not Secure the Minimum points required from Look up Table B then Write 'Not OK.
    (Course Vs Points is listed in Look up table c)

    For 'START & SPEED Completed':
    IF 'START & SPEED' are OK then write 'OK', Else write 'Not OK'.

    For Course completed
    IF 'START & SPEED & Launch' are 'OK' then write 'OK' If not 'Not OK'.

    Thanks a lot for your time!

    Thanks and Regards,
    Dhinesh.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Here is a formula for yielding results for Start, Speed and Launch for the years 2014 and 2015:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula yields expected results for all cells except R3, where I believe the formula is correct because employee 1 has not passed course F (mandatory for 2014 IT).
    Getting a formula for Start, Speed and Launch for the year 2016 may take a bit more time, so I wanted to pass along what has been completed so far.
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    My friend... that is most kind.... I will test it and come back to you....

    Best Regards,
    Dhinesh.

  6. #6
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello My friend.... I m still teasting it.. I will be back to you..
    Last edited by dhineshreddy; 05-19-2017 at 11:56 AM.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    After some limited testing I believe that the following formula will work for the year 2016:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Note that some changes were made to the set up of the Look Up Tables sheet, so that tables B and C are not referenced by the formula.
    Note that the formula yields different results than those given as being expected, however since the values of courses B, C and D are 3, 4 and 5 respectively, the employee's total points exceed the points required for Speed (5) and Launch (9).
    Let us know if you have any questions.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Thank you so much My friend.... How nice of you....
    I think changes you made to the Look up tables make sense.

    I now need to understand what your formula does and leran SUMPRODUCT function...

    those points are just for the example.... it could change when it becomes operational...
    I will test this and let you know..

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I'll wait before writing an explanation in the event that testing shows that one or both formulas need modification.

  10. #10
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello My friend, Firstly my sincere thanks for your help!

    I have tested it now.. Kindly check attached file.

    I have added only One Mandatory and I expect O3 and O4 of Dashboard tab to indicate as OK.
    It appears that formula checked Optional ones as Mandatory.

    I understand that they way how I explained the requirement may have misled you.
    If there are no Optional courses in Look up table, and has passed all Mandatory ones, then it will still be OK.

    For 2016*, We need Trainee to have all the Mandatory ones done + minimum points to be met. It appears that it is also doing mandatory check on optional certificates!

    Would it be possible to help further on this pls?

    BR,
    Dinesh.
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    In the attached file the formula for evaluation of the 2014/2015 courses has been changed so that if there are no optional courses and the employee has passed all mandatory classes in an area (start, speed, launch) the cell will display 'OK', as in the case of O3.
    The reason that O4 didn't display 'OK' was that the formula for 2014/2015 was applied instead of the formula for 2016. If you need a single formula that will check the year before calculating whether the classes taken yield a particular result, that could be done. I had assumed that the formulas could be separated.
    Let us know if you have any questions.

  12. #12
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Sir, Thank you So much for your time! I owe you a lot already for your time.
    I m going through YouTube for Sub product lectures.. getting an idea slowly what you are trying to do... having said that I m not that quick with it.

    'Current Year' is dynamically determined by using another sheet. so we can not guarantee year group being constantly in one cell. So there is no way we could have 2 Separate formulas. is my understanding correct that Formula that is in O3 of Dashboard Tab is a combined formula?

    Many thanks for your time!

    Best Regards,
    Dhinesh.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I have now combined the two formulas placing a conditional =IF(VALUE(LEFT($B3,4))< 2016,... at the beginning. That formula has been placed in O3:P5 as well as R3:R5. Some drawbacks to this approach are:
    1) The formula is now complex and may take some time to decode if it needs to be modified in the future.
    2) The formula assumes that the the rules for yielding the values ('OK', 'Not OK', 'N/A') will remain the same in years subsequent to 2016.
    Let us know if you have any questions.

  14. #14
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Sir.. you are Gem of a Person...

    I admire your patience... Very very kind of you....

    I testing first rather than trying to understand what this formula does. I will surely need to decode it coz I will need to replicate the same logic in my operational workbook.

    Will let you know eitherways.... again... I m very very glad that you picked it up.

    Best Regards,
    Dhinesh.

  15. #15
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jete,

    I appreciate your time...thanks so much.

    As I was testing, I realized that Formula for 2016 needs little more tweaking! A Trainee at Launch Stage will have finished, Course A,B and C as shown in the attachment. and will have secured 10 points. which is > 9 points we specified in O24 of Look up table. But it still writes it as 'Not OK'.

    I assume it is only calculating the points for the trainings in the Row 24!! is it?

    START --> SPEED --> LAUNCH are the phases of employee training. each employee go through all these 3 stages. From Year Group 2016, We are checking the points that a training is worthy of + Specified trainings that trainee must have in the look up table and the Total minimum points being met out of the ONLY trainings specified in the Look up table for the indicator to be OK.

    Ex: Trainee in 2016 will be in START Phase and do Cource A. he has 3 Points. trainee of 2016 batch will do Cource B in SPEED phase will be awarded 3 points. now he has 6 points which is greater than the points we specified at the stage of speed in (O15 of Look up table as attached). so indicator shall be OK. Trainee of 2016 bacth will in LAUNCH phase will do Cource C which is worth 4 points. so at this point Trainee has 10 points and Indicator is OK. in the Attached Examples even if employee has 'Passed' indicator in Dashboard tab for other cources than the ones specified, they would not be counted.

    Please let me know if I can be little more clear.

    Many Many thanks for your time!

    Best Regards,
    Dhinesh.
    Attached Files Attached Files

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Modified formula so that it will sum the points regardless of whether they are accumulated during the start, speed or launch phase. It seems to be reporting results, 'OK' for all three phases, as I would expect.
    Let us know if you have any questions.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Thank you Somuch for your time my friend....

    I have changed Mandatory Certificate as attached. I expect O4 of Dashboard to be 'NOT OK' But it shows as N/A...

    thanks a lot for your patience!

    Best Regards,
    dhinesh.
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Employee 2 has not passed the mandatory course for Start, but has passed some courses, whether mandatory or optional, so O4 should display 'Not OK'. Is that correct, or is it the case that Employee 2 has passed some mandatory courses, just not the one for Start?

  19. #19
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Sir, Employee 2 has not passed Mandatory Course D. in the example I see that he has meets the min points. But Because Employee did not pass the Mandatory cources, he will be NOT OK.

    Hope this clarifies.

    It it is simple, I have added new column in the Lookup table (Number of Trainings needed) for 2016 Scenario, It indeed works if we can check below, Ex: for START
    1. If Employee has Passed All Mandatory Trainings Specified for All START Categories +
    2. Number of Trainings Passed out of the Trainings specified in the Look up table for corresponding START row +
    3. meets the minimum points same as before

    this logic should take care of all possible scenarios such as employee being passed at least 1 training a year.

    If it is easy to simplify or align with previous one, 2014/2015 ones also with newly added column 'Number Of Trainings Needed' we could add number of trainings manually there and do the same check.
    i.e Trainee passed All Mandatory Certificates + matches the number of trainings specified in the column 'Number Of Trainings Needed'.

    It is little bit embarrassing to keep sending it back... so sorry for having to trouble you.
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I probably am the one who should say I am sorry.
    I automated the 'Number of Trainings Needed' column using the function: =COUNTA(C4:N4)
    Not meaning to ignore the part about aligning with 2014/2015 rules, however I think that I have the formula working like you want. If the mandatory courses for 2016 start (or speed/launch) have been passed and the total number of points met/exceeded, then the appropriate cell should display 'OK'.
    If the mandatory courses for 2016 start (or speed/launch) have not been passed but some courses have been passed, and if the number of courses passed meets or exceeds the number of courses specified in 'Look Up Tables' column O, then 'Not OK'. Otherwise 'N/A'.
    I assume that R4 should also display 'Not OK' as the combined points for courses B and C is only 7.
    I hope I have interpreted this correctly and apologize for any embarrassment.
    Let us know if you have any questions, and please do not feel as if it is trouble to me/us.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    thank you Sir! you made be believe that there are people out in the world who don't think 'what for me'.

    Many many thanks for your help... I m starting to test it straight away...

  22. #22
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello sir, I have tested it real quickly... But I think Still there is issue between N/A and Not OK.

    Please find attached. for 2014 IT (Row 3 of Dashboard Tab) where Trainee has 3 Mandatory Trainings but not passed any... It shows N/A indicator which is incorrect.
    in the Row 4, he only has Passed indicator but does not have any Trainings for that phase in which case it should be N/A.

    looks like these 2 parts have been swapped?
    Attached Files Attached Files

  23. #23
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello sir, you can add anything to the Excel to make it work easily....
    I does not have to be in the same structure, I only need to see the status as an outcome. :-)

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I rethought the way that I was trying to do this and think that I have come up with a simpler, hopefully better solution.
    1) In all cases if the value in column O of the Look Up Tables sheet is zero then the formula should display 'N/A'
    2) In the case of the year less than 2016
    a) If all mandatory classes have been passed and
    b) the number of passed courses at least equals the value in column O then the formula should display 'OK'
    3) In the case of the year being 2016
    a) If all mandatory classes have been passed and
    b) the number of passed courses at least equals the value in column O and
    c) the number of points awarded for passed courses is at least the value given in column P then the formula should display 'OK'
    4) All other cases should display 'Not OK'
    To that end the following array entered formula* has been applied to O3:P5 and R3:R5
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is in edit mode.
    I have tested a little and it seems to work, however I encourage you to test it rigorously and let us know if there are any problems/questions.
    Attached Files Attached Files

  25. #25
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello sir, thanks for your time!

    Please check attached...I think there is still a (small) issue with the formula.

    I believe there is an issue with "3) In the case of the year being 2016 --> the number of points awarded for passed courses is at least the value given in column P then the formula should display 'OK'".

    As you can see attached, Employee 2 has passed Courses A,B, and F that are flagged as mandatory and meets the points he is required to meet. But it still shows it as Not OK for START and LAUNCH. For SPEED it shows as OK rightly so.

    I hope it is checking whether employee has Passed Legitimate courses that are specified in the Lookup table. i.e. If Employee has Passed Indicator for Course D, Then Because Course is not in the lookup tables, it should not be counting it.
    Attached Files Attached Files

  26. #26
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Changed the formula to address the issues of "the number of points awarded for passed courses is at least the value given in column P then the formula should display 'OK'" and "checking whether employee has Passed Legitimate courses that are specified in the Lookup table".
    Glad that you are testing to make sure the formula returns the expected results.
    Let us know about any further issues or questions.
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    good morning to you sir!

    Started testing it and looked promising so far. I have had to delete the formula you had put under 'Number Of Trainings Needed' because it is not concidering optional certificates in 2014/2015 Scnarios. trainee must pass one of the optional trainings in 2014/2015 scnarios.

    But looks good sofar. If that calculation is critical for any other thing, please let me know.

  28. #28
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jete,

    I have attached the File. for Employee 2, I expect SPEED and LAUNCH to be OK. because he meets the points and finished the trainings.

    at this point, I would understand if get fed up. Please let me know if you are annoyed with it.

    thanks and Reagrds,
    dhinesh.
    Attached Files Attached Files

  29. #29
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I am not at all annoyed, however I will have to be away from my computer for a day or so, and don't know what your time constraints are. If you are pressed for time then it might be better to start a new thread, linking this one, so that there will be more of a chance to get other members involved.

  30. #30
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jete... I do understand...

    I do have time constraint Need to get it ready for Thursday...once this is tested fine, I have to convert this this logic to my operational workbook.. I hope I am not being too ambitious but no other option.

    Please reply when you get the next opportunity..
    It has looked promising... But I believe issue it has is, it is not considering Optional Certificates!

    Have a Nice day off.

    thanks and Regards,
    Dhinesh.

  31. #31
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Was just checking formula further with my limited knowledge.... formula is not checking for the Term 'optional' at all. That could be the issue! :-)

  32. #32
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jete, Please let e know whn you have access to system..

  33. #33
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    The formula has been broken down into parts that display either true, false or value based on the requirements listed in the first post of this thread. The columns containing these calculations could be hidden for aesthetic purposes.
    1) Checks to see if there are mandatory or optional certificates based on the value in column O of the 'Look Up Tables' sheet (all years) (columns T:V)
    2) Checks to see if the mandatory courses have been passed (all years) (columns W:Y)
    3) Checks to see if the optional courses have been passed (pre 2016) (columns Z:AB)
    4) Checks to see if points have been earned (2016) (columns AC:AE)
    Note: If there are no mandatory or optional courses then "N/A" will be displayed regardless of the values in subsequent cells.
    Note: I have only done limited testing and hope that you will do rigorous testing and let me know if you find any problems.
    Attached Files Attached Files

  34. #34
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Thank you so Much sir.... I was up until 2 AM and thought you might not turn up anymore today....

    I have been pleasantly surprised to see the reply this morning...

    I wil test and let you know

  35. #35
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jete, Unfortunately I could not make a successful test.

    for 2014 and 2015 Scenarios, even if there is No optional Training, formula expects the optional to be in to indicate it as OK.
    Please check attached. I expect Start for employee 1 to be OK.

    Has it been cumbersome to have similar formula as before? I thought we were nearly there with it.

    thank you so much!

    Best regards,
    Dinesh.
    Attached Files Attached Files

  36. #36
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I wondered based on the wording of post #1 if there were times when no optional courses would be assigned. The formula in columns Z:AB has been modified to account for no optional courses.
    I am leaving momentarily and will not be able to work further on this until Monday afternoon my time. If you need this project completed beforehand and if the solution is still lacking, I would suggest opening a new thread.
    I hope that you have a blessed day.
    Attached Files Attached Files

  37. #37
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    thanks for your time! Yes,.. I will need to get it done today...I will seek help elsewhere... thank you...

  38. #38
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Quote Originally Posted by dhineshreddy View Post
    I will need to get it done today...I will seek help elsewhere
    I assume that the attached spreadsheet did not display expected results. Could you show me an example of the results that are incorrect?

  39. #39
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello sir,

    Kindly find attached.

    For Employee 1, I expect START (O3) of Dashboard tab to be NOT OK as I maintained Number of trainings maintained as 2. I know that I have maintained it incorrectly but still checking why it shows as OK as we are checking number of Passed against trainings maintained.

    AND another issue for 2016 case, I expect LAUNCH Phase (R4) to be OK for employee 2 as he does not have any mandatory certificates and Number of certificates to be passed is 0. plus already meets points 10 from START and SPEED Phase against the courses maintained.

    Could you please help me rectify this?

    Thanks and Reagrds,
    Dhinesh.
    Attached Files Attached Files

  40. #40
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I am confused about why the result in R4 is incorrect. In post #1 it states that "If Employee does not have any mandatory or Optional Certificates, then Write 'N/A'." I thought that was why column O was added to the Look Up Tables sheet. Additionally I thought that since there are three optional courses listed on row 24 of the Look Up Tables sheet, that the value in O24 should be one.
    In regard to Dashboard O3, a 'Trainings Needed' section has been added to the Dashboard sheet, to see if the the number of Mandatory and Optional courses passed is at least the value in column O of the Look Up Tables sheet.
    Let us know if you have any questions.
    Attached Files Attached Files

  41. #41
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    I get your point... It got me thinking to just now...

    If you remember (which I surely understand if you don't), this is the reason why I have added new column 'number of trainings needed'.

    for 2016 scenario passed and failed are based on points. for Last phase 'LAUNCH', points from START, SPEED are added up. in the example, Employee 2 has already met the points and we have maintained 'number of trainings to be passed as 0.

    Hope this clarifies.

    thanks and Regards,
    Dhinesh.

    PS: I m testing the latest one.

  42. #42
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    So does the 'number of trainings needed' only apply to 2014/2015 employees?

  43. #43
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jete, please wait for my next comment until you make the change.

  44. #44
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Sir.

    We can keep the current logic as it is. and use columns 'START & SPEED Completed' and 'Course completed' to do this check.

    1. for 'START & SPEED Completed'
    IF START and SPEED = N/A then we write N/A
    If any of them is NOT OK then write 'NOT OK'
    If we have OK with others being N/A, then we write OK.

    2. Similar logic for course completed.
    IF START and SPEED and LAUNCH = N/A then we write N/A
    If any of them is NOT OK then write 'NOT OK'
    If we have OK with others being N/A, then we write OK.

    Thank you so much!

    BR,
    DP.

  45. #45
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    This could be the formula for Q3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This could be the formula for S3 and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  46. #46
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello sir,

    Kindly find attached. I Expect R3 to be not OK.
    Because he has not passed one of the optional certificates + minimum certificates to be passed has not met. only passed 2 out of 3.

    Much appreciated!
    Dhinesh.
    Attached Files Attached Files

  47. #47
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Modifications to Trainings Needed and Optional (pre 2016) seem to have that issue sorted.
    If you select AB3 and use the Evaluate Formula feature, from the Formulas tab, you'll see that it evaluates to 2>=3 (meaning 2 of the required courses were passed out of the needed) before the final evaluation of False.
    Select AE3 and use the Evaluate Formula feature to see that two optional courses are listed on the Look Up Tables sheet and that zero have been passed resulting in False for that cell as well.
    Attached Files Attached Files

  48. #48
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jetc.. It has seemed to be fine in the first iteration... I m on Furthur testing...

    I will need to convert the formula into my operational worksheet whose configuration is different... So learning formula what you used is a must to me..and is something I want to skill myself up.

    I have seen that for some formulas you have used flower brackets. when I get into the EDIT Mode, then flower bracket is gone. Could you please let me know what does that flower bracker do. Tried to learn INDEX formula but the example I tried does not need Flower bracket..

    Thank you ever so much..

  49. #49
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Sir,

    Kindly Find attached. I expect R5 to be NOT OK. Because Employee 3 at LAUNCH stage has not attained 9 Points needed.

    Would you be able to check pls?

    Thanks and Regards,
    Dhinesh.
    Attached Files Attached Files

  50. #50
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    'Flower Brackets' are automatically placed by Excel when an array entered formula is activated by simultaneously pressing the Ctrl, Shift and Enter while the cell is in edit mode.
    I can see why cell R5 is reporting incorrectly, as AH5 is counting two points for Optional course G in all three levels (Start, Speed and Launch). Is it possible that once a course has been labeled as Optional in a level for a particular group of employees, such as 2016 Finance, that it will not be listed again. If that is not possible, I'll look into a work around, however that may not be as easy as some of the other changes we have made.

  51. #51
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Ohh no.... I thought I covered this scenario to be tested in my iteration 1 and worked fine...

    Courses are flagged as Optional such as in 2016 finance case. employee can choose to do optional course in START phase or SPEED or LAUNCH Phase. completely up to him depending on his time available. that is same courses are available for trainee in all 3 phases. But once Trainee has passed, employee will not re do the same course in another phase.

  52. #52
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    please read above sentence as 'that is why same courses are available for trainee in all 3 phases. But once Trainee has passed, employee will not re do the same course in another phase. '

  53. #53
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    The work around is that another table is added in rows 31:39 of the Look Up Tables sheet. This 'helper' table shows whether or not a course will count for points. The array entered formula in columns AF:AH is then modified to make use of this table.
    Attached Files Attached Files

  54. #54
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello Jete... thank you... I can see it is complex now.... Especially for me to understand the logic and replicate it in operational workbook at my work...

    I m testing it... Sorry to bother you.... how long more have you got?

  55. #55
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I will be around, off and on, for the next several hours.

  56. #56
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Hello sir,

    is your name Jate? :-)

    I m testing it.... I see that you tried to have this logic for 2014, 2015 and for mandatory courses.

    This will only be the case for 2016 scenario. will it help avoiding the new table you had in place in anyway?

    would it be possible to let me know brefly what this new table does? so that it becomes easy for me to focus on what I need to test.

    BR,
    DP.

  57. #57
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    The new table is populated with the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula looks at the rows of the existing table, in the same column to find whether 'Mandatory' or 'Optional' is present in the row corresponding to the row heading of that in which the formula has been written (2014 IT etc). Excel's convention is that if the value of the logical_test is zero, the logical_test is regarded as False, otherwise the logical_test is regarded as True. If the logical_test is True the cell displays 'Points', if False then it is left blank. Only the formula for 'Points (2016)' uses this table.

  58. #58
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    thank you ..... My initial testing seems ok... I m starting iteration 2 now... 12:30 AM....

    you are back again on Monday is it?

  59. #59
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I may be back by Friday but almost certainly by Monday (June 5th)
    Do you need any of the other formulas explained, or does the Evaluate Formula feature make them easy enough to follow?

  60. #60
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    thank you thus Far... I m testing it though... I would appreciate if you could drop a note before leaving...

    I will try best way to understand the formulas... I not technical enough to understand it through evaluate. Tried though.

    If you have time, can you please explain lil bit on flower brackets? does it matter not to have them? or they are only visible when you use CNTRL+SHIFT+ENTER?

  61. #61
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    The array entered formula* that populates the 'N/A' section, columns T:V is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula looks for the number in column O of the 'Look Up Tables' sheet that is in the row corresponding to the row header of the 'Dashboard' sheet and the column header of the 'Dashboard' sheet. For example in the file attached to post #53 the formula in Dashboard!T3 looks for the number in the row of the 'Look Up Tables' sheet having a 2014 IT in column A and Start in column B. The value found is 2.
    *Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula (typing in curly braces doesn't work). If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  62. #62
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Thank you... If I want to put this formula in my workbook referencing different cells, do I need to click on the Formula and CTRL+****+ENTER?

    If I just copy paste the formula and not press CTRL+SHIFT+DELTE, it wont work?

    Sorry for my ignorance... I may have made you regret for picking this up...

  63. #63
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Only thing that gets me thinking is, only some have Flower brackets and some did not... How do I know which one to activate and which one not to...

  64. #64
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    Only the array entered formulas, the ones that populate the 'N/A', 'Trainings Needed' and 'Points (2016)' fields, are array entered. The formulas for 'Mandatory' and 'Optional (Pre 2016)' are regular formulas which will activate when you press the enter key.

  65. #65
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    one last question coming your way sir... testing...

  66. #66
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    The formula that populates the 'Mandatory' field is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Using W3 of the file attached to post #53 as an example, the first Sumproduct makes a count based on the following matches
    1) 2014 IT in column A of the 'Look Up Table' sheet
    2) Start in column B
    3) Mandatory in the range C4:N30
    4) Passed in the third row of the 'Dashboard' sheet.
    So it is counting how many mandatory courses a 2014 IT employee has passed at the Start level.
    It then tests to make sure that is equal to the number of cells containing 'Mandatory' in the fourth row of the 'Look Up Tables' sheet by repeating steps 1 - 3 with the second Sumproduct. In this case 1=1.
    Let me know if this doesn't make sense.
    I'll be back in a short while to write more explanations.

  67. #67
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    that's very kidn of you sir...

    I was worried about scenario where 2 courses are optional for one employee in 2 different phases. which one indicates as YES if number of trainings is maintained as 1.
    It is not working as desired but I know that there is no input in the file that indicates in which phase employee has passed. So I assume we are fine as you took care of it by adding new table...right?

  68. #68
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: How to use Multiple conditions in one cell in a dashboard

    I am not sure that I follow what you are saying. The table at the bottom of the Look Up Tables sheet shows whether or not there are points available for an employee regardless of the phase. I would suggest setting up a scenario that shows what you want and if the result isn't what you want post that particular question in a new thread (you could link back to this thread as a reference as to how you got to this point).
    Continue with the explanations of the formulas using row 3 of the Dashboard sheet as attached to post #53:
    The array entered formula* for Z3 (Trainings needed) is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The Sumproduct portion of the formula calculates the number of courses for a 2014 IT employee, in the Start phase, based on either mandatory or optional courses assigned and passed. The Index portion finds the number in O4 of the Look Up Tables sheet.
    The formula for AC3 (Optional) is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The If portion determines whether or not the employee is pre 2016, if so the formula continues, if not it displays False. If the formula continues and either there are no optional courses offered or the employee passes at least one of the optional courses that is offered, the formula displays True. If there are optional courses offered and the employee doesn't pass at least one the formula displays False.
    The array entered formula* for AF3 (Points) is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The If portion of the formula determines if the employee is 2016 or later and if so continues, if not it displays false. The Sumproduct portion calculates the points based on the table at the bottom of the 'Look Up Tables' sheet as well as row 3 on that sheet and row 3 on the 'Dashboard' sheet. The Index portion of the formula finds the number of points that employee should have to pass that phase.
    The formulas for columns O, P and R are all similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The IF's are evaluated in turn so that once one evaluates to true the rest or ignored.
    The formulas for columns Q and S are similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    AND means that all arguments must evaluate to true in order to display 'N/A'. OR means that any of the arguments could evaluate to true in order to display 'Not OK'. If neither of the IF conditions evaluates to true the formula displays 'OK'.
    I hope that this makes sense and is helpful.
    If you have questions, I'd suggest starting a new thread to ask them.
    I hope that you have a blessed day.

  69. #69
    Forum Contributor
    Join Date
    05-18-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    126

    Re: How to use Multiple conditions in one cell in a dashboard

    Thank you So very much...... its not easy to have same level of patience all the while.... the way you helped on this query is highly appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  2. Multiple Conditions in a cell
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] Multiple Conditions in a cell
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  4. Multiple Conditions in a cell
    By Maya in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1