+ Reply to Thread
Results 1 to 7 of 7

Need help with complicated IF THEN formula with several fields to consider

  1. #1
    Registered User
    Join Date
    12-15-2023
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    8

    Need help with complicated IF THEN formula with several fields to consider

    I hope I can describe this adequately. I have a daily audit where tons of rows of information will be inputted. I have a tab (Compensation Reduction) that will calculate a penalty EACH time a doctor does not complete his report on time. The penalty amounts vary depending on the type of test and the number of days the report is late (this is calculated in the tab, Price List). I was able to setup most of my formulas but now I am stuck on how to tell the cell to pull the exact reduction rate based on the service and # of days late. Also, there will be several instances where the doctor is going to be listed in the Compensation Reduction spreadsheet more than once. How do I capture each entry from the Daily Audit tab as a unique instance in the Compensation Reduction tab, so it does not override one of the previous instances? I'm guessing I will need a formula in the Compensation Reduction tab for column A to pull the psychologist's name from the Daily Audit tab with a rule that it will pull it each time as a unique instance. I just don't know how to do that. I've uploaded an example of the form to help with my explanation.
    So I have two issues if someone with expert Excel knowledge can help me! Thank you!

  2. #2
    Valued Forum Contributor
    Join Date
    02-04-2017
    Location
    chennai
    MS-Off Ver
    MS OFFICE 365
    Posts
    440

    Re: Need help with complicated IF THEN formula with several fields to consider

    my understanding is that i will apply the formula You can use a combination of the doctor's name and a unique identifier, such as a timestamp, to create a unique instance identifier. Assuming the doctor's name is in Column B of the Daily Audit tab, you can use the following formula in the Compensation Reduction tab (Column A) to create a unique identifier
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This formula concatenates the doctor's name with the current timestamp.

    2. Pulling Information from Daily Audit tab to Compensation Reduction tab
    Assuming you want to pull information from the Daily Audit tab into the Compensation Reduction tab based on the unique instance identifier, you can use the VLOOKUP or INDEX-MATCH function.
    For example, if the unique instance identifier is in Column A of both tabs, and you want to pull the information from Column C of the Daily Audit tab to Column B of the Compensation Reduction tab, you can use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    3. Calculating Penalty Based on Price List
    To calculate the penalty based on the service and the number of days late, you can use nested IF statements or the LOOKUP function. Assuming the service is in Column C and the days late is in Column D of the Compensation Reduction tab:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This assumes that the service in the Compensation Reduction tab matches the service in the Price List tab, and the penalty is calculated as the product of the penalty rate and the number of days late.

    Remember to adjust the cell references according to your actual spreadsheet layout and data range.
    Last edited by sudbhavani; 12-16-2023 at 04:42 AM.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,917

    Re: Need help with complicated IF THEN formula with several fields to consider

    Administrative Note:

    Where you have provided a workbook solution, please also include a clear explanation of what you've done and how (including formulae/code as approriate) in the post in question (for the benefit of members unable to download attachments).

    This is about making your assistance accessible to all.

    Thanks.

    EDIT: Thanks for the explanation, but you have now removed the sample workbook! Please put the workbook back - you need both the workbook AND the explanation. Thanks.

    EDIT AGAIN: Thank you.
    Last edited by AliGW; 12-16-2023 at 05:13 AM.
    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.

  4. #4
    Registered User
    Join Date
    12-15-2023
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    8

    Re: Need help with complicated IF THEN formula with several fields to consider

    Thank you so much! I was able to do the unique identifier for each doctor's instance. But for #3, this is not taking into account my Price List tab. If you see the different columns, there are different dollar reductions based on the number of days late and the type of test. Please place the formula in the Compensation Reduction tab, under column F (reduction). The formula would have to somehow recognize that in column d, the late days and column e, the service. Then it would have to associate those with tab Price list, rows C2 thru J2: C13 thru J13 and A3:A13 to make sure it's pulling the right penalty for the right test.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,372

    Re: Need help with complicated IF THEN formula with several fields to consider

    This proposal is adds a row to the Price List sheet. The added row could be moved and/or hidden for aesthetic purposes.
    The values in the added row are: 2, 3, 4, 365, 2, 4, 6 and 365
    The formula for column F on the Compensation Reduction sheet could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    12-15-2023
    Location
    Chicago
    MS-Off Ver
    365
    Posts
    8

    Re: Need help with complicated IF THEN formula with several fields to consider

    Thank you, JeteMC! This is exactly what I needed!!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,372

    Re: Need help with complicated IF THEN formula with several fields to consider

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. [SOLVED] Complicated formula...for me anyway. :)
    By dshamp16 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2018, 10:17 AM
  2. Formula Help (Probably Not Complicated)
    By Jrm7586 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-28-2013, 12:26 AM
  3. Replies: 1
    Last Post: 03-26-2013, 07:46 PM
  4. Complicated use of IF formula
    By donkeybusiness in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-13-2012, 06:17 AM
  5. Need a complicated IF formula (maybe?)
    By lroffler in forum Excel General
    Replies: 2
    Last Post: 10-10-2011, 01:42 PM
  6. IF formula help..little bit complicated
    By jgy6000 in forum Excel General
    Replies: 1
    Last Post: 03-02-2011, 09:13 AM
  7. Rather Complicated Formula
    By paulmaddock in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-08-2008, 09:36 AM

Tags for this Thread

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