CalcNovice
New Member
- Joined
- May 29, 2011
- Messages
- 6
A first time poster here. I'll begin with a disclaimer: I'm using Calc from OpenOffice, not Excel, but I gather that it covers the same basic functions as Excel very closely, and I suspect that my problem doesn't require those higher functions. If it does, I'll understand when you tell me to take a hike!
I want to take a list of integers (for all intents and purposes random numbers between 1 and 100,000) and add every member of that set to every other member, making a table of the results. There are about 150 numbers in that set, but I want to be able to add to it over time. From the results I want to see whether, by chance, any of those results are identical to any of the numbers in the original data set. Ideally then I'd like to find a way to make the spreadsheet automatically indicate for every number in the original set that also appears by chance in the results, which other two numbers in the original set added together to create it.
I have done the first stage easily enough by pasting the data set along the top row and left column, making a little addition formula and creating a field of results. I've also rather laboriously and no doubt clumsily managed to achieve the basic goal of finding which two original numbers add up to make the third (using the COUNTIF, MATCH and OFFSET functions), but it requires some manual data input each time. I can go into what I've done in more detail if need be, but I suspect that starting with a clean slate would be better than reviving my efforts.
My question is how best to achieve this automatically and dynamically, so new data can be added in due course, with no manual work in the meantime.
The ideal end result would be a sheet with a column listing my original data set, where for each number that appears by chance also in the results field, the two other original numbers of which it is the sum appear next to it in columns B and C.
As my username suggests I am a novice. Thanks in advance for your time.
- Pete
I want to take a list of integers (for all intents and purposes random numbers between 1 and 100,000) and add every member of that set to every other member, making a table of the results. There are about 150 numbers in that set, but I want to be able to add to it over time. From the results I want to see whether, by chance, any of those results are identical to any of the numbers in the original data set. Ideally then I'd like to find a way to make the spreadsheet automatically indicate for every number in the original set that also appears by chance in the results, which other two numbers in the original set added together to create it.
I have done the first stage easily enough by pasting the data set along the top row and left column, making a little addition formula and creating a field of results. I've also rather laboriously and no doubt clumsily managed to achieve the basic goal of finding which two original numbers add up to make the third (using the COUNTIF, MATCH and OFFSET functions), but it requires some manual data input each time. I can go into what I've done in more detail if need be, but I suspect that starting with a clean slate would be better than reviving my efforts.
My question is how best to achieve this automatically and dynamically, so new data can be added in due course, with no manual work in the meantime.
The ideal end result would be a sheet with a column listing my original data set, where for each number that appears by chance also in the results field, the two other original numbers of which it is the sum appear next to it in columns B and C.
As my username suggests I am a novice. Thanks in advance for your time.
- Pete
