Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find out which units in a list equal a certain amount

Posted on 2012-03-16
2
Medium Priority
?
196 Views
Last Modified: 2012-03-16
Hello experts,

I want to buy some assets listed below in Table A

Table A
 12,40  
 12,60  
 14,10  
 14,70  

I can buy those assets with the values from Table B

Table B
 0,10  
 0,20  
 0,50  
 1,00  
 2,00  

I would like a formula or vba code that can calculate the way i can do it

e.g.

Table A      Solution
 12,40          6x2,00 - 2x0,20
 12,60          6x2,00 - 3x0,20
 14,10          7x2,00 - 1x0,1
 14,70          7x2,00 - 1x0,50 - 1x0,20


For the first asset i would need six of two and two of 0,2 and so on..


Thanks in advance
0
Comment
Question by:kpyrgos
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 37728322
Enter Table A in A3:A6
Enter Table B in B2:F2 and in "Descending order"
Enter this formula in B3
=INT(($A3-SUMPRODUCT(--$A$2:A$2,--$A3:A3))/B$2)
copy this formula down and across
0
 

Author Comment

by:kpyrgos
ID: 37728346
Thank you sir!!

I never knew sumproduct could be used with "--".
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question