# 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. we've got a deal.

• ## 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

• ## 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.

• ## Complex Sports Fixture algorithm

Re: Complex Sports Fixture algorithm

Thanks guys. I've now found a solution using a posting from TinyJack in response to a thread titled "Extracting Combinations".

Cheers
Robert

• ## Complex Sports Fixture algorithm

I'm trying to set up a spreadsheet to organise a Sports Fixture such that each team competes with each other team only once.

I had initially thought the solution would be simple but after some research I've found quite complex formulae apply.

I'm trying to apply the formula referred to in http://www.cs.colostate.edu/~m…nal/massey_parallel97.pdf (5 An Optimal Factorization Algorithm).

Does anyone know how to code this in vba?

• ## 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?

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

• ## Vlookup does not work & Wildcard available?

Re: Vlookup does not work &amp; Wildcard available?

Rather than adding spaces you should make certain that entries DO NOT have extra spaces (or other characters) after either the source or destination values.

• ## Vlookup does not work & Wildcard available?

Re: Vlookup does not work &amp; Wildcard available?

You do not have the EXACT same value typed in. One of the values has a space following the text.

• ## "Re-Displaying" Named Ranges

Re: Multiple &quot;For ... Each&quot; 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 &quot;For ... Each&quot; 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

Try the attached ...it should point you in the right direction.

Note the use of a dynamic named range called "CPI_Table" (this will range will grow as entries are added to the bottom.

See on the main sheet how the purchase date is looked up.

• ## 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

BrandTrock. .....you beat me to it...a different version of the same approach

• ## 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.