+ Reply to Thread
Results 1 to 40 of 40

Formula that gives different results according to the selected client profile

  1. #1
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Formula that gives different results according to the selected client profile

    Good morning,

    I am trying to create a formula in an Excel sheet that will apply different criteria based on the client profile selected in cell D4 and the score at
    I34.


    The sheet has several tabs with the same risk rating (low, medium, and high) but different risk scores.

    The current formula is designed for Profile A, but I need to modify it so that if Profile B is selected in D4, the risk rating in cell I35 and the recommended action in cell I39 will automatically change according to the parameters of Profile B tab

    Moreover, if the selected profile is B, I need to apply a discount of -5 points.

    I'm attaching the spreadsheet for reference.

    I appreciate any help you can provide.

    Best,


    Andry
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,068

    Re: Formula that gives different results according to the selected client profile

    Haven't got time to do this properly but I was playing with this.

    Reorder the two tabs so the low values run from low to high, not high to low like they are now
    Then

    =LOOKUP(I34,IF(D34="Profile A",N35:N37,N42:N44),IF(D34="Profile A",L35:L37,L42:L44))

    You may be able to do this via VLOOKUP without reordering the tabs but I didn't get time to check.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    thank you. Unfortunately, I cannot reorder the tabs as the tool I'm using is already approved and sent to the Regulator.

    Perhaps is there another way to amend the formula without touching the current settings?

    Also, please note that the formula you provide does not change according to the selected profile in cell D4

    Thank you!

  4. #4
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Any further help is appreciated. thank you.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,792

    Re: Formula that gives different results according to the selected client profile

    Try

    in I35

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    2 Named ranges "Profile A" and "Profile B"
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #6
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Thank you the formula is ok but the score colour and action is nor reflect the results of profiles A or B.
    E.g. if the score is 63, it is high risk and the action is escalate to CAC. however in case of profile B it is medium risk but the action should not be escalate to CAC but to onboard

  7. #7
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Also please note that in I39 there also other conditions to met as set in column N and O
    Thank you

  8. #8
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    any update please? thank you

  9. #9
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Good morning John,

    I was wondering if you could help me with fixing the formula in cell I39 of the spreadsheet. The aim is to correctly reflect the risk score based on profiles A and B. As per my previous message, the formula should consider different profiles, whereas for profile A, the result "Escalate to CAC" should be displayed when the score is 60.01 (high risk). However, for profile B, the same action should only apply at 65.01. Please note that there are already some exceptions set in the current formula in the spreadsheet which shall remain. Any suggestions on how to modify the formula would be greatly appreciated.
    Thank you.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

    Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

    (Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  11. #11
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Hi Ali,
    Yes, I was not aware of that. My apologies. I've asked the moderator from the other forum to remove the post. Are you still keeping this request open?
    Thank you
    Andry

  12. #12
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    https://www.mrexcel.com/board/thread...2#post-6157792

    That's the link with my (same) request of support.

    Best.
    Andry

  13. #13
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Good morning Ali,
    Just to know, are you keeping my post open?
    Thank you

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    You have provided the link requested and you are still able to post here - so what do you think?

  15. #15
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    thank you
    I hope some good soul can support with this post

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    Be patient, please.

  17. #17
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    yes no problem thank you

  18. #18
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Hi Ali, is there an indicative timeline for reviewing the post? John kindly provided a formula which is ok but remain the last part to fix.
    Thank you
    Best,
    Andry

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    Nope - it's entirely up to anyone who feels able and willing to assist you. Nobody can be compelled to help you. Timelines are for paid services - this is a free forum, and as such, the user cannot have any expectation of timely assistance, or of any assistace at all. Take it or leave it, but it is what it is.

    PS The thread has had over 300 views so far - given that you haven't even had a nibble either here or on the pother fporum, the odds of anyone helping are getting less, I'm afraid, but fingers crossed.
    Last edited by AliGW; 02-23-2024 at 06:14 AM.

  20. #20
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Thanks Ali. You are already doing great so of course nobody can complain
    Mine was just to understand if pending posts are usually reviewed in a short period.
    Best
    Andry

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    It has been viewed over three hundred times.

  22. #22
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    yeah and I assume is a complicated one..

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    I've put out a call for assistance: if that doesn't get a response, then you are probably out of luck this time.

  24. #24
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    thanks, Ali, appreciate your help anyway

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,792

    Re: Formula that gives different results according to the selected client profile

    I suggest you change I39 to

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Thank you John I have updated the new formula. The issue is for the action in case of selection of Profile B whereby the action is not reflecting the risk bands of Profile B.
    E.g. 65 is a medium risk for profile B and the action is not to escalate but to onboard (unless there are those conditions stated in column N already applicable to profile B) thank you!

  27. #27
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,792

    Re: Formula that gives different results according to the selected client profile

    I don't follow the logic: if any profile is "Medium Risk" why is the escalation different? Illogical to me

  28. #28
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    the logic is that high risk must be escalated so since the risk bands are different for profile A the the escalation starts at 60.01 but for profile B it is set at 65.01

  29. #29
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,792

    Re: Formula that gives different results according to the selected client profile

    a "High Risk" is a "High Risk" whatever values derive it so this should lead to the same escalation choice (IMHO).

  30. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    Try this in I36:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AliGW; 02-25-2024 at 04:01 AM.

  31. #31
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    John (and Ali) the formula in I36 is correct. The last part is to align the result in I39 according to I36 and the risk categories for profile A and B.
    so in case of profile A being selected the formula works well but in the case of profile B the high risk starts at 65.01 so below that, the result is onboard (unless one of the same exceptions as for profile A is met).

  32. #32
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    No, the formula in I35 was NOT correct. It was taking only one profile into consideration.

    You need to use my previous formula, and then this CORRECTED formula in I39:

    =IF(N12="Prohibited","Refuse Client",IF(I35="High Risk","Escalate to BoD",IF(I35="Medium Risk","Escalate to CAC","Onboard")))
    Attached Files Attached Files

  33. #33
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    Thanks, Ali but the last formula is not correct. the escalation to CAC or BOD for profile A is already defined in the formula I provided. the formula should include the same conditions for profile B except that the escalation which refers to the risk category profile B applies from 65.01 (high risk) as that is the first number of that band.
    Moreover, as I mentioned in the post, for profile B only the formula should also include a discount of 5 points e.g. if the client score is 40 the result in I34 should be 35
    Please let me know if everything is clear. thank you

  34. #34
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    OK - then I can't help you. None of what you have said makes any sense to me at all. What I have given you produces the results that I would expect given the two profiles that you provided.

    I cannot create a solution when I do not follow the logic (which is flawed, in my opinion). This is probably why you've had no assistance here. Good luck!

  35. #35
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,792

    Re: Formula that gives different results according to the selected client profile

    Try

    in I34

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  36. #36
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    I believe the logic behind it is quite simple, Ali. The spreadsheet is utilized to determine the risk profile of clients for two different products. Profile B is considered less risky than profile A, which means that the category risk bands are different when compared to profile A. The formula should first reflect the risk category, which is classified as high, medium, and low. Based on the risk, the following action is to escalate the client to different committees accordingly.

    However, based on your formula, the risk score band for profile B does not match the action required, as clients with a score of 65 or below do not require escalation, but can be directly onboarded.

  37. #37
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    I'm out, Andry - John has offered a solution.

  38. #38
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    thank you John. it works. the last piece is the formula in I39 to reflect the criteria I mentioned above to have the appropriate action (onboard, escalate to CAC or BoD) according to the score/risk rating I35 already fixed by Ali.

  39. #39
    Forum Contributor
    Join Date
    04-15-2021
    Location
    Malta
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 64-bit
    Posts
    181

    Re: Formula that gives different results according to the selected client profile

    ok Ali thank you anyway

  40. #40
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Formula that gives different results according to the selected client profile

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

+ 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: 03-01-2023, 05:18 PM
  2. Replies: 4
    Last Post: 01-24-2018, 06:09 PM
  3. [SOLVED] Client Data in Columns - how to create a client drop down
    By abearmenta in forum Excel General
    Replies: 9
    Last Post: 10-10-2016, 10:24 AM
  4. [SOLVED] Moving variable data from multiple same-client records to one client record
    By jkilday4 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2015, 02:32 PM
  5. Need Client Search Form with distinct results
    By Gwen@YouWantWhat? in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2012, 06:08 PM
  6. Create a macro to send mail from selected profile
    By vivek_work in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2012, 04:15 PM
  7. save column wizard results in template/profile?
    By tpatput in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-01-2008, 02:50 PM

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