+ 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
    7

    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 NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,626

    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

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and 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 Expert
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    2,329

    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
    7

    Re: Randomise items and total their associated cell values

    Thank you very much for your help Aardigspook & Phuocam!

  5. #5
    Forum Contributor
    Join Date
    10-09-2017
    Location
    Ro
    MS-Off Ver
    2013
    Posts
    100

    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
    7

    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 NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,626

    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)

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