+ Reply to Thread
Results 1 to 4 of 4

Tried formula variations including "if", "sumif", "min", "match" but can't get it to work.

  1. #1
    Registered User
    Join Date
    11-19-2020
    Location
    Trondheim, Norway
    MS-Off Ver
    16
    Posts
    2

    Tried formula variations including "if", "sumif", "min", "match" but can't get it to work.

    Hello,

    I would really appreciate if someone could help me with the example attached.

    Column A lists potential regions to import from, and column B lists distances from those regions to my location (Trondheim). Column C lists the quantity produced in those regions, and in Column D I have a target of x kilos that I wish to import. In column E I have a CO2-factor that converges km*kg to kgCO2 produced. I want to import from the regions closest to me (shortest distance), and combined, the quantity from those regions should match my target of x kilos.

    To do this I need a formula that 1) sorts out the lowest values in column B, 2) sums up the values of the adjacent cells (quantity) until target value for quantity is reached, and finally, 3) all cells in column B that gets counted should be multiplied with their adjacent cells and then multiplied with the CO2-factor, before they are all summed together (distance x kg x CO2-factor).

    The purpose is to find the lowest number for CO2-emissions.

    Thank you in advance,

    Bjorn
    Attached Files Attached Files
    Last edited by bjorn123; 11-20-2020 at 05:23 AM. Reason: Change of title

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,623

    Re: Need help with formula in Excel

    Hi Bjorn, and welcome to the forum,

    Please read the yellow banner at the top of the page. Or skip to extended version of it below:

    Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, merged cells, some important cells filled with content by macro/manually/by formula, etc. - please show them all, or at least indicate in text). The use of unrepresentative data and formats is very frustrating and can lead to long delays in reaching a solution.

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

    3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

    To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

    PS. The solution probably would be easier in Excel but I think it is still manageable in '16 version. Is it OK to use some helper column in solution? It would probably make life much easier (and formulas shorter). Just probably - I've not started solving, as there is no sample file.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    11-19-2020
    Location
    Trondheim, Norway
    MS-Off Ver
    16
    Posts
    2

    Re: Need help with formula in Excel

    Thank you for responding, Kaper.

    I have added an xlsx file with the sample data to the attatchments now.

    Regards,

    Bjorn
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,387

    Re: Need help with formula in Excel

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

+ 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. [SOLVED] VBA, User-defined formula argument referring to cell value calculated by Excel formula
    By ARAGORN II in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2017, 01:57 PM
  2. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  3. Replies: 10
    Last Post: 12-16-2015, 03:16 PM
  4. Replies: 2
    Last Post: 03-21-2014, 11:43 AM
  5. Macro: Applying formula to multiple cells in excel (formula editing)
    By city in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2012, 06:41 AM
  6. Replies: 0
    Last Post: 03-19-2012, 07:32 PM
  7. Replies: 1
    Last Post: 03-04-2012, 12:03 AM

Tags for this Thread

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