+ Reply to Thread
Results 1 to 6 of 6

Creating a variable price sheet

  1. #1
    Registered User
    Join Date
    11-17-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2013
    Posts
    5

    Creating a variable price sheet

    Hi there,

    I attempted looking in other areas for the info I need, but have been unsuccessful. I am attempting to create a price calculator for my editing business. What I want to be able to do is enter in the Word count, as well as the number of pages. These two items will directly affect the price. So, if Cell B1 (Word Count) is up to 50k then the price is .006 per word. If it is up to 80k then the price is .0065. Anything over 80k then the price is .007. In addition, there will be a fee based on page numbers. The page number price is much easier at a simple .10 per page.

    As if that didn't seem complicated enough, there will be four different "types" of edits. The prices by word count above reflect the price on "contextual" editing. There will be 2 other versions- Copy editing and then Proofreading which will have different prices.

    Copy- .005, .0055, .006
    Proof- .0035, .004, .005

    Anyone know how to make this work? Do I need to use a vlookup function?

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Creating a variable price sheet

    something like this maybe
    Attached Files Attached Files
    Last edited by martindwilson; 11-17-2013 at 04:37 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Creating a variable price sheet

    This is how I understand your problem.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Creating a variable price sheet

    Hi and welcome to the forum

    Try this approach.

    1st, make a table of rates...
    F
    G
    H
    I
    1
    Words Contex Copy Proof
    2
    0
    0.006
    0.005
    0.0035
    3
    50000
    0.065
    0.0055
    0.004
    4
    80000
    0.007
    0.006
    0.005

    then create a 2nd table something like this...
    A
    B
    1
    Words
    90000
    2
    Type Copy
    3
    Cost per
    0.006
    4
    Pord Cost
    540
    5
    Pages
    1
    6
    540.1

    B3=VLOOKUP(B1,$F$2:$I$4,MATCH(B2,F1:I1),1)
    B4=B3*B1
    B6=B4+(B5*0.1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    11-17-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Creating a variable price sheet

    Thank you all so much! I kind of took bits and pieces for each suggestion and made a easy to use price matrix. Thank you thank you thank you!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Creating a variable price sheet

    happy to help and thanks for the feedback

+ 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 formula/sheet with a fixed variable
    By RemanBill in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-24-2013, 03:29 PM
  2. need help with creating a sheet to calculate sales price
    By waseem.1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2013, 12:20 AM
  3. Question abuot creating a price quote from a long price list
    By glennchung in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-02-2009, 03:49 PM
  4. VLookup - creating a variable Sheet reference?
    By futbol1097 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2007, 01:57 PM
  5. Creating Variable for a Sheet
    By kyle in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-30-2006, 11:40 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