+ Reply to Thread
Results 1 to 13 of 13

item price sorting

  1. #1
    Registered User
    Join Date
    09-28-2012
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    21

    item price sorting

    hi all

    i got a question, not sure if this is in right section (mods please move to relevant if its not)

    so i got a shopping list of items and prices, i want to pay partly by card and some in cash

    i got for example £7.80 on my debit card and £1.50 in cash

    is there a formula or something i can use that would output/show items totally near as much to £7.80?

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: item price sorting

    with great respect, I do not understand your question.

    What exactly are you trying to achieve, perhaps if you were to post a sample workbook with some input data and expected results you would find someone who can help resolve the issue you face.

    Hopefully this sample workbook will also show us volunteers what you have already tried before you posted your question??

    Jmac1947

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: item price sorting

    There is Solver of cource an Excel Add-In not installed by default when you set up Excel but you can always add it later.

    To do so click on "Office Button" -> "Excel Options" -> "Add-Ins" bottom left in the "Excel" window that opends find "Manage" and select
    "Add-Ins" in the drop-down box and click "Go". In th "Add-Ins" window that opends find "Solver Add-In" and tick box in front.

    Now you are ready to go. I've set up a small Solver model for you. To test clear rang D3:D14 and run Solver. Found under "Data" tab top right.

    Click Solver and then click "Solve". Don't forget to clear D3:D13 before activating Solver.

    Alf
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-28-2012
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: item price sorting

    thanks for help guys

    i tried to add the solver but it came up with this error

    [URL=http://i.imgur.com/vmOr8tY.jpg[/URL]

    no files open, the solver.xlam is there

    this is what i need

    the list of available items on the left, i select how many i want of each, and as you see the max i can spend (7.80), what excell does is then sorts a list of items in a separate sheet/column showing the items and quantities and total value

    http://i.imgur.com/pY7d0PE.jpg
    Attached Images Attached Images
    Last edited by matskiuk; 03-03-2014 at 09:07 AM.

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: item price sorting

    Perhaps this link may be of help?

    http://www.youtube.com/watch?v=lVVBxKUP_CU

    Or perhaps the problem is that path to solver is the one from my PC where I have both Excel 2003, 2007 and 2010 installed + that I'm running Windows 7 professional 64 bits.

    If you already have solver installed perhaps you could try to set up your own model. You can copy my layout to an Excel file from your PC and see if you can start Solver.

    The setup is simple. Target cell is C18 (set to minimum), cells to change is range "D3:D14", constraint range "D3:D14" = Binary and under "Options" check box marked "Assume Non-Negative"

    Alf

  6. #6
    Registered User
    Join Date
    09-28-2012
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: item price sorting

    ive done that, it did exactly the same but them came up with the error at the end, and the solver is not in the data tab

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: item price sorting

    You could try going to the add-in manager and unselected solver, got out of Excel and then go back in and selected it again, and see if this fixes the problem.

    You could also try to search for the solver.xlam file to see if it's on your pc at all.

    Alf

  8. #8
    Registered User
    Join Date
    09-28-2012
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: item price sorting

    ive done that, it is in the correct folder, ive checked to see if its read only etc

    ive tried copying it to desktop

    when i get to the tick box popup , solver is always unticked, when i tick it, it immediately comes up with the error

  9. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: item price sorting

    Never heard about a problem like this before bur googling I'v found this comment

    The problem was not installing 'Visual Basic for Applications' before, when I installed Office 2007.
    VBA is in Office Shared Features group.
    Just insert your Office installation disk, select 'Add or Remove Features' and then VBA.
    After that Solver can be installed in Excel.
    Perhaps this may help you and then there is a link as well to a thread. In post #2 there is a reference to a hotfix (underlined) that will take you to a microsoft page explaining the hotfix where they say

    This hotfix fixes the following issue: 957386
    Error message after you set a network path as the default file location in Excel 2007 and then you click the Data tab: "\\<Path>\solver.xlam cannot be found. Check your spelling, or try a different path"
    and in post #3 there is a description for fixing the problem your self (not for the faint of heart) by modefying the registry.

    http://forums.techarena.in/windows-software/1377237.htm

    Best of luck to you in fixing this problem.

    Alf

  10. #10
    Registered User
    Join Date
    09-28-2012
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: item price sorting

    ok ill try that

    thanks for help

  11. #11
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: item price sorting

    Found this googling for Solver problems :

    When a workbook has a reference to an add-in, Excel will store this reference with its entire path. Now, when a workbook gets opened in Excel, and it has a reference to an add-in, which has not been opened yet, Excel will try to open the add-in, from the path that was stored in the reference. If this is not successful (for example, the workbook was saved on another machine with a different file location) Excel will add the "Missing: solver.xla" reference. To fix this, you would have to open solver.xla (or solver.xlam) by invoking Solver from Excel, before opening your workbook. Then open your workbook and save the workbook.
    So you could try first to open and close Solver in Excel before you open the file I uploaded.

    Alf

  12. #12
    Registered User
    Join Date
    09-28-2012
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: item price sorting

    ahhh well, i opened the file before i tried solver, so now i cant use it at all, as i am unable no matter what i try to load solver, even if a worksheet is open or not

  13. #13
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: item price sorting

    Dear oh dear going from bad to worse! I hardly know if I should give you any more advice. At this stage I would probably do an uninstall and then reinstall of Excel after saving all my Excel work files to a safe location. Perhaps you have somebody that you can discuss this problem with?

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 08-28-2012, 09:44 PM
  2. how to select price according to item and quantity
    By astrok in forum Excel General
    Replies: 3
    Last Post: 09-26-2010, 03:06 PM
  3. Replies: 8
    Last Post: 08-06-2010, 07:27 AM
  4. Look up item to find price.
    By gibbsmachine in forum Excel General
    Replies: 1
    Last Post: 12-02-2009, 02:24 PM
  5. Retrieving the Price for a Specified Item and a Specified Brand
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-25-2005, 02:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1