Which formula should be for finding this number's alphabet

[shanis]

Hello everyone, Good day,

I am searching a formula, sheet is attached. in R5=680, in S5 which formula should be write so that it find its matching / vlookup number from Z6:AA21
example, 250+220+170+120+80=680 so that in S5 answer should be P,O,M,K,H, cuz these alphabetic values are equal to R5 value.

Waiting for your guidline.

Thank you!

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[BMV]

shanis Why not 250 +220+200+ 10=680?
And if you use 2013 then beter to use UDF.

[Richard Buttrey]

...another option an array formula entered with Ctrl-Shift-Enter

Please Login or Register  to view this content.

Perhaps a more pragmatic solution would be to use TextToColumns to expand the values in the cell across columns and use a regular sumif()

[shanis]

Thanks all for your cooperation.

Please find the attached sheet, still have some errors. Kindly guide.
thanks again.

[BMV]

shanis once again .Why 240 is 70+80+90 but not 220+20 or 200+40 ….
125 - ?????

[davsth]

The problem is unclear. I suspect it may be to be done by solver or some vba code

To sum to the value is clear
However several combinations can achieve the answer. which one is the best answer? or will any answer do?

[Richard Buttrey]

The formula needs to go on row 9. It's looking to analyse the characters in M9. The original formula had a couple of relative cell refs not absolute. This formula - entered with Ctrl Shift Enter puts that right. See attached. Results are in P9:P12. Cut and paste them to J9:J12 if necessary.

Please Login or Register  to view this content.

[Alf]

A solver setup to find the combination that matches a givet target value, but as davsth writes there may be more solutions but solver will only find one. Solver model is setu to find an
exact match.

There is a VBA code written by Tushar Mehta that will analyse a string of numbers and make a table of the numbers that adds up to a given target. Was a thread about that. will see if I can find it.

Alf

[Alf]

To illustrate the point made by davsth about multiple solution I've uploaded the file with Tushar Metha's macro and a improved result layout by forum member John Topley, but I
could not find the thread.

Cell D2 you put a number for the number of combinations you wish. A 0 will give you all possible combinations the macro can find.

Cell D3 is where you put the target value. Range D4:D19 contains the values you can use to find the combinations. This range should be sorted from highest to lowest.

Select range D2:D19 and click run.

Value in cell F1 I can't remember what they mean but value in F2 starts with target value, time when the macro was run and the numbers that adds up to target value i.e.
1 = 240, 2 = 220, 10 = 70 and so forth.

Alf