+ Reply to Thread
Results 1 to 7 of 7

Randomise items and total their associated cell values

  1. #1
    Registered User
    Join Date
    02-21-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    9

    Randomise items and total their associated cell values

    Hi,

    I have a list of locations and distances for those locations. I wish to randomise 4 of those locations and sum their stances. I also wish to list the 4 random locations in one cell sperated by a comma.

    I have the 4 random locations using the formulae =INDEX($A$3:$A$24, RANDBETWEEN(1, ROWS($A$3:$A$24)))

    For example:

    Destination Distance

    Location 1 12 Location 2 [Formulae for Total Distance of those 4 Locations]
    Location 2 9 Location 7
    Location 3 4 Location 1
    Location 4 30 Location 5
    Location 5 20
    Location 6 17 [4 Random Locations in one cell seperated by a comma]
    Location 7 19

    I would appreciate any help.

    Thanks,
    Verifyor
    Attached Files Attached Files
    Last edited by Verifyor; 01-03-2018 at 09:40 AM.

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Randomise items and total their associated cell values

    To list your four locations, use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you want a comma and space, just put a space after each comma.

    For the summed distances, try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Randomise items and total their associated cell values

    Total Distance for those 4 Locations:

    =SUMPRODUCT(SUMIF(A3:A24,C3:C6,B3:B24))

  4. #4
    Registered User
    Join Date
    02-21-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Randomise items and total their associated cell values

    Thank you very much for your help Aardigspook & Phuocam!

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Randomise items and total their associated cell values

    if you want to randomize and concatenate in multiple cells and sum their adjacent values:

    in C3:
    Please Login or Register  to view this content.
    in D3:
    Please Login or Register  to view this content.
    If you received a satisfactory solution, please mark the thread as solved. Thanks.

  6. #6
    Registered User
    Join Date
    02-21-2014
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Randomise items and total their associated cell values

    Thanks Paul I will try use the formulae to randomise & concentrate in multiple cells with adjacent totals.

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Randomise items and total their associated cell values

    Glad we could help and thanks for marking the thread as Solved.

+ 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. [SOLVED] How to randomise staff for roster
    By kophanz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-12-2016, 04:10 AM
  2. Replies: 6
    Last Post: 11-15-2015, 04:53 PM
  3. [SOLVED] need vba help to sum the overall total ,>2 days total & change cell values
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-17-2014, 01:55 PM
  4. Total values in MOD grouped items
    By theearthvolta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-13-2010, 03:06 PM
  5. Replies: 1
    Last Post: 08-29-2010, 10:51 PM
  6. Replies: 11
    Last Post: 09-10-2008, 01:27 AM
  7. [SOLVED] Formula to total items from a drop list with values in several col
    By Rescues Mittens in forum Excel General
    Replies: 1
    Last Post: 07-29-2005, 09:05 PM

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