Posts by r_hind

  • Calculate final sales price for a desired profit

    This is a case of getting the maths clear..


    For example ...


    Lets assume the selling price = 1.00 (or 100%) and the selling price = all costs and all commissions and the desired profit.


    If thew desired profit = 25 cents in every dollar then the assumption is that costs and commissions are 75 cents and the markup % is 33%.


    Are you able to calculate the total of costs and commissions THEN add your desired markup?


    Of course ..if the commissions are based on the end selling price it is a little more complicated but the same rationalse applies and it may be necessary (but not good practice) to enable iterative calculations (Options / Formulas).


    From a maths (algebra) perspective you should be able to apply the maths in either direction.


    Let me know if I can help further.

  • AUD $30.00 to identify travelling (road) distance from and to 80 locations

    Re: $AU30 to identify travelling (road) distance from and to 80 locations - REOPEN


    Wigi,
    I'm happy to pay you via Paypal on the basis that the payment doesn't attract the fees that paying internationally via a bank transfer would normally attract. I will accept transfer costs (fees) no greater than 10% (ie a total cost to me of max $AU33.


    Is that okay with you?


    Cheers
    Robert Hind

  • AUD $30.00 to identify travelling (road) distance from and to 80 locations

    I have a spreadsheet containing 80 locations (all map coordinates in and around the city of Perth in Western Australia. The spreadsheet is set up as a matrix (80 "from" locations, down the side and 80 "to" locations across the top) BUT the from and to locations are the same (ie distance from location 1 to location 2 is the same as the distance from location 2 to location 1).


    The distances must be in kms and be the same as those that Google Earth or Google Maps would identify through their find directions options.


    I'm prepared to pay $AU30 (by electronic transfer to a bank account in Australia) to someone who completes the lower half (below a diagonal line) of the matrix by 10am WA time on November 12, 2010.


    Regards
    Robert Hind
    EDIT: no emails please

  • Maintain Dynamic Range References After Row Insert

    Re: Maintain Dynamic Range After Row Insert


    I think perhaps you need to review the process you're using, inparticular why you're INSERTING rows (which would be a perfectly good approach for stretching an otherwise fixed/static named range).


    A Dynamic named range works best when you're adding items to the list of values (ie filling in another cell). This doesn't involve or require the insertion of rows.

  • Formulas : Creating a fixture list on Excel

    Re: Formulas : Creating a fixture list on Excel


    Bigbob,
    I've been looking for a solution like yours for some stuff I'm doing putting together fixtures for various numbers of teams.


    I've tried to replicate your solution but suspect I'm misinterpreting your instructions because I just can't make it work.


    Does the same principle work for any number of teams? Or is it necessary to "pad" our uneven numbers with a number representing a bye?

  • Linked Spreadsheets

    Re: Linked Spreadsheets


    Something else to watch for ...if you built the spreadsheet by moving or copying worksheets from another spreadsheet you may inadevertently have created links through use of named ranges (if the spreadsheet from which you copied or moved a worksheet had named ranges applied).


    So .. it is possible that the sheets you INTEND to link still remain BUT if the original spreadsheet (from which you moved or copied) worksheets has been deleted or renamed then this may explain the fault you're experiencing.


    Look through the named range dialog box and if you see named ranges that identify an association with a specific folder then the problem I've described may be the reason ....but you may need to look at the dialog box while in each workssheet. Hope this is isn't too confusing.

  • VBA Function: One range as input, two ranges as output

    Re: VBA Function: One range as input, two ranges as output


    Can you post an example or illustration?


    The way I would generally approach this sort of situation would be to apply a named range (typically a dynamic named range) to the inputs. I would read the dynamic named range into an array, perform the calculations then send the output array back to a [wider] named range.


    Generally I find this approach results in very quick code.


    Cheers
    Robert

  • "Re-Displaying" Named Ranges

    Re: Multiple "For ... Each" Loops


    I'm not quite sure what you're trying to do...but it sounds like you want to choose from a list of coordinators against each job.


    If so have you tried using (in the Coordinators column) using the data validation function (on the Toolbar > Data, Validation). Select allow List, then show the reference list as being =Coordinators (refering to the named range).


    Is this heading in the right direction.

  • VBA/Excel

    Re: VBA/Excel


    and a better subject heading will help achieve a quicker response... VBA / EXCEL is too broad .....every single posting in this forum should be about VBA / EXCEL!!!

  • "Re-Displaying" Named Ranges

    Re: Multiple "For ... Each" Loops


    Also see the spreadsheet I posted (a few minutes ago) in response to the posting "CPI Indexing- Formula".


    Once you've opened the spreadsheet ..go Insert, Name, Define and look at the code associated with "CPI_Table".

  • CPI Indexing- Formula

    Re: CPI Indexing- Formula


    So you're using CPI figures to discount the value of the goods to today's terms?


    A quick solution would be to look the purchase date up in a single column look up table. You'll need to ensure that the date (including the day and month ...so you can span quarters) is formatted (as a date and not just text) in both the source column and in the look up table.


    Try looking up examples on Lookup, Vlookup and Hlookup (good examples on this site).


    hope this helps.

  • Pivot table or formulas......help

    In regards to the data not being correct that is only because the "Count" rather than "Sum" function has been used. Use the pivot table wizard to fix this.


    In every other aspect BrandTrock's advice is correct. If you're going to analyse pieces of information (records) you need to make sure that each record is complete (all fields filled in on each line) so that analysis will return the right results.


    Sure you can get round this through programming or code but the code will have to work hard and with high risk of error if you haven't structured the data correctly.


    First rule - get the structure right (fields, field value type (don't mix numbers and text ...ie don't show "5 days" as a field).


    Second rule - don't leave gabs - no blank rows or columns


    Hope this is of help.