+ Reply to Thread
Results 1 to 7 of 7

Formula for matrix pricing

  1. #1
    Registered User
    Join Date
    03-29-2016
    Location
    Missouri
    MS-Off Ver
    2007
    Posts
    4

    Formula for matrix pricing

    I am working on a formula for a matrix pricing...
    I set up a sheet to make quick estimates with a labor time (then calculates a cost) parts and adds Tax. Keeps the errors down from using a calculator.

    I currently have the matrix charts set up as
    Grid price 1.png

    With this at the bottom to calculate
    Grid price 2.png

    On paper you can follow each and get the total. but I want to use the Estimate sheet to add taxes and such in for a total.

    I want to be able to put say 2.1 in B4 on Est sheet and get the total from the Grid (D11) of $233.10. That I can do but i want to put is any variation of B9-B24 and C8-L8 and get the corresponding total
    here is what the Estimate sheet looks like.
    Grid price 3.png

    I have tried many different combinations with not any good results. Thanks in advance for any help.
    Jared

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Formula for matrix pricing

    Please upload an example workbook (Go Advanced>Manage Attachments).
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-29-2016
    Location
    Missouri
    MS-Off Ver
    2007
    Posts
    4

    Re: Formula for matrix pricing

    I think I have attached it.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Formula for matrix pricing

    Okay, so currently, you are returning $273 which is hrs * fixed labor cost (in J1 of Easy EST. Yes? Instead you want to use the grid in Grid Pricing?
    =INDEX('Grid Pricing'!$C$9:$L$24, MATCH(INT('Easy Est'!B4), 'Grid Pricing'!$B$9:$B$24,0), MATCH(MOD('Easy EST'!B4,1), 'Grid Pricing'!$C$8:$L$8))
    Is that what you are looking for?

  5. #5
    Registered User
    Join Date
    03-29-2016
    Location
    Missouri
    MS-Off Ver
    2007
    Posts
    4

    Re: Formula for matrix pricing

    that is correct on how I want it.

    but I want it to show the total not Multiply it by the hours. With this Formula if I input 1.5 it take 1.5 * 157.50 but the total should be 157.50.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Formula for matrix pricing

    Not sure where you are putting this formula.
    If 1.5 is in Easy Est B4, and this formula is in 'calculations only B2
    it will return $157.50.

  7. #7
    Registered User
    Join Date
    03-29-2016
    Location
    Missouri
    MS-Off Ver
    2007
    Posts
    4

    Re: Formula for matrix pricing

    Ok I got it now. So if I want to be able to add all B4:B19 and make it total off that I will need to do-

    =INDEX('Grid Pricing'!$C$9:$L$24, MATCH(INT('Easy Est'!B20), 'Grid Pricing'!$B$9:$B$24,0), MATCH(MOD('Easy EST'!B20,1), 'Grid Pricing'!$C$8:$L$8))

    I made a separate spot on 'Calculations only' for the total and the added it instead of 'Calculation only b18' and it works.

    Thank you so much!

+ 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. Need help creating a pricing matrix based on width and height
    By alisssa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-10-2015, 07:24 PM
  2. [SOLVED] Need help filling in formulas for a pricing matrix
    By FinGhost in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2014, 03:21 AM
  3. Determining unit price from a pricing matrix...
    By pmantey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 10:34 PM
  4. [SOLVED] Trying to compare current pricing and costs with suggested pricing
    By dearnne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2013, 07:01 PM
  5. Pricing Guide/Chart with variable pricing increases
    By HSDesigns in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-23-2013, 04:49 PM
  6. [SOLVED] Calculate Pricing from Start and End Date with different Pricing Weight for Each month
    By xyang06 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2013, 02:54 AM
  7. Conditional Pricing Matrix
    By turnech2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-24-2012, 11:58 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1