+ Reply to Thread
Results 1 to 10 of 10

If formula to total with criteria not working

  1. #1
    Registered User
    Join Date
    07-06-2021
    Location
    Exeter, England
    MS-Off Ver
    Office 365 in app
    Posts
    8

    If formula to total with criteria not working

    Hi all, 1st post
    I have been wrestling this for hours and am seeking help before my mind melts please.
    Here is the Data
    ExcelDat.png
    Here are the formulae so far
    ExcelFor.png

    The 2 gray are additional hours worked.
    All 3 Adj columns and the Worked work!
    The Total does not and will not whatever I do

    It needs to be
    if contract = 37(fulltime) it is simply the 2 adj added together
    if contract + hours worked <37, it is simply the adjusted hours
    however if contract + hours worked >37, it is the extra hours worked above contract + the extra adj hours NOT INCLUDING THE HOURS ADJUSTED BY

    Does that make sense!? It's got me completely befuddled.
    Last edited by AliGW; 07-06-2021 at 09:14 AM.

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,699

    Re: If formula with a challenging calculation attached

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    07-06-2021
    Location
    Exeter, England
    MS-Off Ver
    Office 365 in app
    Posts
    8

    Re: If formula with a challenging calculation attached

    Here it is with sheet and sample results
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,050

    Re: If formula to total with criteria not working

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new, I have done it for you today.)
    Ali


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


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: If formula with a challenging calculation attached

    Please add the manual calculations for the yellow cells: I find this confusing
    the extra adj hours NOT INCLUDING THE HOURS ADJUSTED BY

  6. #6
    Registered User
    Join Date
    07-06-2021
    Location
    Exeter, England
    MS-Off Ver
    Office 365 in app
    Posts
    8

    Re: If formula to total with criteria not working

    Quote Originally Posted by AliGW View Post
    Administrative Note:

    Welcome to the forum.

    (Note: this change is not optional. As you are new, I have done it for you today.)
    Thank you AliG, I will do an edit just to see what you meant.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,050

    Re: If formula to total with criteria not working

    No need - as I said, I have done it for you.

  8. #8
    Registered User
    Join Date
    07-06-2021
    Location
    Exeter, England
    MS-Off Ver
    Office 365 in app
    Posts
    8

    Re: If formula with a challenging calculation attached

    Quote Originally Posted by JohnTopley View Post
    Please add the manual calculations for the yellow cells: I find this confusing
    Sooo if a p/t member normally works 30hrs basic, and then 15 o/t. The o/t adjustment is 1.5 for any hours worked >37.
    But Total is the o/t worked + extra hours resulting from the 1.5 adjustment, not including the >37 hours used for the adjustment (whcih is where I get confused)
    Sooo 30 +15 is 45, 8 above 37 so adjusted hours would an extra 4 for 12.
    Total is 15 + extra 4 accrued.

    Sorry, part of my issue is I can't seem to express it concisely.

  9. #9
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,522

    Re: If formula to total with criteria not working

    H3: =IF(B3=37,G3,IF(B3+C3+E3<37,C3+E3,(B3+C3+E3-37)*1.5)), copy down

  10. #10
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,522

    Re: If formula to total with criteria not working

    If your adjusted hours mean OT hours * 0.5, then formula: =IF(B3=37,G3,IF(B3+C3+E3<37,(C3+E3)*(1.5-1),(B3+C3+E3-37)*1.5))

+ 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. revenue sheet based on the daily calculation ( attached sample )
    By Islamhmelkady in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-03-2019, 06:07 AM
  2. [SOLVED] Challenging Kestrel Logger Data Separation and Calculation
    By ryanclose in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-14-2017, 10:18 AM
  3. Challenging Groupby-Like Formula
    By asleischow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2017, 03:07 AM
  4. Help with challenging array formula
    By chuji in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2013, 02:11 AM
  5. Need Help with a challenging formula.
    By gamiensrule in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2010, 10:05 PM
  6. [SOLVED] Add an amount from a calculation to a cell with a calculation (Sample attached)
    By aiwnjoo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2010, 10:41 AM
  7. Challenging Formula
    By Gordon in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-23-2006, 04:30 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