+ Reply to Thread
Results 1 to 4 of 4

Quantity Surveying Cost Database

  1. #1
    Registered User
    Join Date
    10-05-2020
    Location
    Lincoln
    MS-Off Ver
    2013
    Posts
    2

    Quantity Surveying Cost Database

    Good afternoon,

    I'm a new QS and new to these forums and in order to price jobs easier as I obtain more costings I am trying to compile an ongoing costsheet.

    I've attached an example as a reference point; a table in Master Cost Sheet which has been allocated a code, e.g,

    Row 3 - Code = A0001 Desc = Facing Brick Qty = 10 Rate = 1.8

    In budget test, I would like it to cross reference against the master cost sheet and choose the rate which is associated to the closest quantity equal to or lower than.

    E.g, a quantity of 71 would be closest to Qty 50 thus show a rate of £1.6

    Help/advise would be really appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Quantity Surveying Cost Database

    Something like below?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This assumes Quantity is sorted ascending.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    10-05-2020
    Location
    Lincoln
    MS-Off Ver
    2013
    Posts
    2

    Re: Quantity Surveying Cost Database

    Quote Originally Posted by CK76 View Post
    Something like below?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This assumes Quantity is sorted ascending.
    Yes that's the result I want but given I expect there to be hundreds of codes, I would like the formula to select the rows containing a particular code, e.g, A0001, rather than manually specifying the rows myself as this would be a very time consuming process once the spreadsheet has been fully built.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Quantity Surveying Cost Database

    Then something like...
    Please Login or Register  to view this content.
    Edit: since return value is numeric only, you can simplify it to...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by CK76; 10-05-2020 at 11:11 AM.

+ 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. Static Cost multiplied by user defined quantity
    By Mookie88 in forum Excel General
    Replies: 1
    Last Post: 10-09-2019, 02:52 PM
  2. Replies: 3
    Last Post: 04-06-2013, 01:37 AM
  3. Replies: 2
    Last Post: 02-08-2013, 04:08 PM
  4. Need to calculate minimum lot quantity with a changing variable cost
    By BCarson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-28-2012, 09:38 AM
  5. Excel 2007 : Quantity Surveying
    By qs24 in forum Excel General
    Replies: 0
    Last Post: 11-04-2011, 01:55 PM
  6. Quantity Surveying Issue
    By Therock1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-03-2010, 10:41 PM
  7. Pivotable Sum of Cost times Quantity
    By Jim Pockmire in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-16-2006, 08:00 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