Looking For An Excel Equation

[BrknFngrs]

Does anyone know how to setup an excel spreadsheet so that I can plug in an X number list of values and then a "target value" and have excel determine which numbers (from those I have entered) can be summed to give me my target?

In case my above explanation isn't clear, here's a basic example.

If I enter a list of numbers:
10
13
18
7
14

And a target of 20; I want excel to highlight (or do something else to notify me) that the only numbers from my list that will sum to 20 exactly (right to the penny) is the 13 and the 7.

Any help would be much appreciated.

[realazy]

The best way is to write an while loop in macros or even if statements.

BUT if you really would like to stick with regular functions I think you would be able to use the =IF() function and embed more IF() functions , but I think the maximum times you can embed is 5 or 6.


for example:


A1: 10
A2: 13
A3: 18
A4: 7
A5: 14

=IF(A1+A2=20,"A1+A2", IF(A1+A3=20,"A1+A3", IF(.... etc.

but I just realized that you can only do the first interval with this method. I guess you can have an equation for each number then do the round robin for each number. It would limit you to 5 or 6 numbers max though.

You can obviously put your target number as a cell as well, that way you don't have to edit the equations.

BUT I would stick with macros.

:P

[megavolt]

Originally posted by BrknFngrs
that the only numbers from my list that will sum to 20 exactly (right to the penny) is the 10 and the 13.

Any help would be much appreciated.

10 + 13 = 20?

But ya... pretty much have to a recursive check with a macro.

[megavolt]

Actually... think I figured out another way if you're interested. PM me your email and I'll send you the excel file.

[adam c]

Originally posted by megavolt


10 + 13 = 20?

But ya... pretty much have to a recursive check with a macro.

uhh....

he wants the spreadsheet to figure out which numbers from a range will equate to 20.. in this case it would be 7 and 13 not 10 and 13

[megavolt]

Try reading his original post again... and I thought I was tired...

[rc2002]

Originally posted by realazy
The best way is to write an while loop in macros or even if statements.

BUT if you really would like to stick with regular functions I think you would be able to use the =IF() function and embed more IF() functions , but I think the maximum times you can embed is 5 or 6.

I've nested more than 5 or 6 if statements in a cell before.

Writing a few lines of code in VBA would be your best bet.

[Grogador]

Sounds complicated, pay an Asian kid to do your algebra homework for you instead...

[BrknFngrs]

Thanks for all the help guys, I suspected macros or VB was the way to go but I'm not too familiar with either.

Megavolt PM'd

[megavolt]

Ug... it's on the work laptop. I'll try to break it out later.

[BrknFngrs]

^^ Oh no problem, sorry it took me so long to respond; I was away from the computer all day.

[FiveFreshFish]

Some things to consider:

- What if there's more than one solution?
- Can the list of numbers grow or shrink?

[Dumbass17]

try www.mrexcel.com
i joined the forum for on specific equation i was having trouble with
and within the day i had my answer
(mine was based on conditional formatting but still a tough question to answer)

[megavolt]

Email sent. The solution I sent should be scalable and pick out multiple solutions. But it will all depend on the ultimate use of this, which wasn't really made clear.

[BrknFngrs]

^^ Thanks for the sheet, I suspect it would work but I wonder if something with macros would work better for my specific needs.

To clarify, it will be used to determine which values from a general ledger are making up an account balance. Ideally, I want to be able to copy a range of values from a general ledger into the sheet, input my target value (per financial statements) and have it tell me the possible combinations of items that will yield that value.

So scalability is major concern, as well format as I want to be able to drop in a large set of numbers with just a simple copy and paste. Ideally, I'd love to have it tied to a "button" I can just push so it doesn't require changing settings/equations every time.

I have a working sheet using the solver function in excel but its fairly "clunky". If anyone wants to see it or could possibly build a "button" into it just PM me and I can email it you.

Thanks again for the insight.

Originally posted by Dumbass17
try www.mrexcel.com

I'll try this as well, thanks.