+ Reply to Thread
Results 1 to 4 of 4

Average total cost on a tiered pricing table

  1. 04-14-2024, 07:38 AM #1
    Littleowl
    Littleowl is offline
    Registered User
    Join Date
    04-14-2024
    Location
    Cyprus
    MS-Off Ver
    MS Office 365
    Posts
    1

    Average total cost on a tiered pricing table

    Hi All, hope you can help me

    Can you please help with the formulas to find the average total cost a client will pay if they purchase initially let's say 13,000 and then also if this quantity increases/decreases along the way?

    Tier 1: from 1 to 5,999 at $10.50
    Tier 2: from 6,000 to 7,999 at $10.00
    Tier 3: from 8,000 to 9,999 at $9.50
    Tier 4: from 10,000 to 12,499 at $9.00
    Tier 5: from 12,500 to 14,999 at $8.50
    Tier 6: from 15,000 to 19,999 at $8.00
    Tier 7: from 20,000 or above at $7.50


    Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-14-2024, 09:48 AM #2
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,005

    Re: Average total cost on a tiered pricing table

    Try this

    Please Login or Register  to view this content.
    PS. D6 should be (7999-6000+1)*C6 = 20000. D7,D8,etc like this.
    Last edited by windknife; 04-14-2024 at 09:54 AM.
    Reply With QuoteRegister To Reply
  3. 04-14-2024, 09:58 AM #3
    MohamedGadAllah
    MohamedGadAllah is offline
    Registered User
    Join Date
    02-29-2024
    Location
    EGYPT - Cairo
    MS-Off Ver
    MS365
    Posts
    88

    Re: Average total cost on a tiered pricing table

    Hi,
    Welcome to the forum.
    Detailed steps are as follow:
    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    F
    G
    1
    		 upper limit cost per unit for each tier total quantity purchased quantity purchased total cost average cost per unit total cost
    2
    5999
    10.5
    13000
    5999
    62989.5
    9.90
    128748
    3
    7999
    10
    2000
    20000
    9.90
    4
    9999
    9.5
    2000
    19000
    9.90
    5
    12499
    9
    2500
    22500
    9.90
    6
    14999
    8.5
    501
    4258.5
    9.90
    7
    19999
    8
    0
    0
    9.90
    8
    20000
    7.5
    0
    0
    9.90
    Sheet: Sheet1 (2)

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

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

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

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

    G2:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Thank,
    Mohamed GadAllah
    Reply With QuoteRegister To Reply
  4. 04-15-2024, 02:22 AM #4
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Average total cost on a tiered pricing table

    Two formulas are given . If required use.
    For Grand total in J5
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    For average in J6
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate cost based on distance from gate, max cost per person and a total cost.
    By Sulzeer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-08-2023, 11:15 PM
  2. [SOLVED] Tiered pricing
    By densoes in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-12-2022, 08:24 AM
  3. Formula for tiered pricing based on an average
    By fatcow_81 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2021, 01:53 PM
  4. Function to calculate total cost based on a tiered fee structure
    By Trevorino in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-06-2021, 07:51 AM
  5. [SOLVED] Dynamic Tiered Pricing Table
    By mtardif in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2020, 12:50 PM
  6. Formula for Tiered pricing with various pricing matrix
    By kunaltalreja in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2020, 11:37 AM
  7. [SOLVED] Trying to add tiered pricing to a CSV
    By rbsteve in forum Excel General
    Replies: 8
    Last Post: 07-01-2015, 04:57 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1