Can excel find the missing numbers for _+_=2 ?

[JohnPaulino]

Hello,

I need excel to find the numbers missing in _+_=2 from a range of numbers.

In other words, I have a specific value and I want excel to search through a range and find the cells that add up to my criteria.

Your help would be very much appreciated!

[zbor]

Can you upload example with your input(s) and desired output?

[JohnPaulino]

Definitely zbor, here's an example of what I mean:

Inputs
Range
A1. 1.00
A2. 1.00

Sum (criteria)
2.00

Output>> Cells A1 and A2

[jwright650]

So...all wholes numbers

Any and all whole numbers that can be summed to equal the target.

Will your targets always be single digit numbers or not?

[zbor]

I ment more like do you have positive and negative numbers (like -3+5=2) or maybe decimal (like -0.5+2.5=2) etc.

[JohnPaulino]

The items in the range are $ currency, so the inputs are always in the form 0.00. The inputs might be $1,458.87..$5.09...$1,245,098.32. The numbers will always be positive or zero, but never negative.

The target, or criteria, in the same fashion will always be a $ currency and never negative.

Yes, any or all numbers in input can be summed to equal the criteria. For example, 1.00 + .50 + .50 = 2.00 etc...

[zbor]

Maybe something like this:

[jwright650]

@zbor,
the RANDBETWEEN() function doesn't work in XL2003.


EDIT: my bad...it was returning the #NAME on my machine.(I don't have the Analysis ToolPak add-in)

[ChemistB]

Yes, it's in the analysis addin I believe.

[JohnPaulino]

That's just about right, but the output is just Desired Value - Range Value. This would work if the Desired Value - Range Value equaled another item in the range.

In other words, I'm looking for both numbers in the example _+_=2

[ChemistB]

Okay but if the number you're summing to is 4 do you want returned
1 + 3 and
3 + 1 or just one or the other?

[JohnPaulino]

Chemist B,

That's right, just one or the other. For 1+3 and 3+1, I just need one of them to come up

The main issue is finding two values in the range ( which is a very large range ) that sum up to my specified criteria.

[ChemistB]

And you want all possible combinations?

[zbor]

That's just about right, but the output is just Desired Value - Range Value. This would work if the Desired Value - Range Value equaled another item in the range.

In other words, I'm looking for both numbers in the example _+_=2

Well, first number is in A column, and in B column is number if it's match. That would give you both numbers.

[ChemistB]

If you only want a single answer, then (assuming your result is in C1)
In A1
=RANDBETWEEN(0,C1*100)/100
In A2
= C1-A1

[JohnPaulino]

@zbor

Maybe I didn't see, but does the number in Column B exist in Column A? If it does, then that's exactly what I'm looking for. And also if it does, can the output indicate which cell of Column A that the numerical output of Column B matches with?

[JohnPaulino]

And yes, all possible combinations.

[zbor]

Yes, it exist. That's what MATCH part of formula do.

And other part, is this it:

[JohnPaulino]

Yes that's exactly it!

I found one small error, It seems there is a discrepancy of two cells in the C column. I'll use one example from the sheet you sent me.

There is an output of $7.00 in B12, and the corresponding output cell in Column C is A148. However, the output cell should be A150.
The same is true when you go to B150, output of 3.00. The corresponding output cell is A10, when it should really be A12.

[zbor]

I see difference is for 2... Then I've probably take wrong starting reference. Put A:A instead

[JohnPaulino]

Thank you zbor for you help! That was very smart.