+ Reply to Thread
Results 1 to 2 of 2

4 rates, if week reaches over 50 then Flat rate value ,Range date

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    London
    MS-Off Ver
    Excel 2013, 2016
    Posts
    95

    4 rates, if week reaches over 50 then Flat rate value ,Range date

    Hi,

    I am after 2 formulas or 5 different formulas to fit my needs on

    Formula (1 or 4):
    4 Weekly range:
    First 3 hours of the week: 0 to 3 -> 1
    4 to 21 -> 2
    22 to 36 -> 3
    36 to 60 -> 4

    Formula ():
    If sum of hours per week reaches over 50 hours -> flat rate value of 200 for the full week (Monday to Friday)
    Alternative: If days are part of the specific range of date -> flat rate value of 200.

    Tips: Ideally, based on above formula, I will create a report (Pivot Table)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: 4 rates, if week reaches over 50 then Flat rate value ,Range date

    Something like

    =IF(A1<4,A1,3)+IF(A1<22,MAX(A1-3,0)*2,34)+IF(A1<37,MAX(A1-21,0)*3,42)+IF(A1<60,MAX(A1-35,0)*4,0)
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

+ 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] Calculate yearly rent with escalating rate or flat rate w/diff starting year
    By Nguyen-er in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-26-2017, 11:02 AM
  2. Replies: 2
    Last Post: 02-09-2016, 10:40 PM
  3. Replies: 7
    Last Post: 11-24-2015, 11:44 AM
  4. Flat Rate Tier Calculation Formula HELP!!!
    By ltomlin3210 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-17-2014, 06:13 PM
  5. Replies: 1
    Last Post: 05-16-2014, 10:10 AM
  6. [SOLVED] What function can I use to add a flat rate plus an hourly rate?
    By dosmin777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2014, 02:26 AM
  7. Tiered Commission - Flat Rate
    By rjhendrix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2012, 08:38 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