+ Reply to Thread
Results 1 to 7 of 7

Formula to compare a sum of a range to a cell value, and return cell count of range used

  1. #1
    Registered User
    Join Date
    06-03-2014
    Posts
    3

    Formula to compare a sum of a range to a cell value, and return cell count of range used

    Hello - I am new to the forum, so I hope that I am doing this properly. If not, I apologize..

    I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to and would like to see if anyone might be able to help me some.

    Mini.xlsx

    Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.

    Thanks for any help on this!

  2. #2
    Registered User
    Join Date
    06-03-2014
    Posts
    3

    Re: Formula to compare a sum of a range to a cell value, and return cell count of range us

    I am still trying to work this out, and have come closer with understanding how index and match work together, but cannot wrap my mind around how to make the sum of the demand stop based on the amount of on hands.

    Any one have any ideas? Or even a different format that would come up with the same result of how many weeks covered there would be?

    Thanks everyone!

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Formula to compare a sum of a range to a cell value, and return cell count of range us

    Hi kerri2000

    I have also puzzled over this and come to the conclusion that I cannot do it with a formula.

    So I did it using a macro. This works OK, but I had some interesting thoughts when it came towards the end of the dates, so if there were no more weeks of data, I used the average. It seems to work, but the macro has been designed to only be used on rows 2 - 4. If you want to cover more rows - let me know how they are laid out.

    Having done this, I then thought that if you were prepared to accept some element of estimation, then it could easily be done, based on the average of a fixed number of weeks. I have tried your data over various weeks and found that you get a pretty close result, based on a 4 week average.

    Let me know what you think of the attached.

    Regards
    Alastair
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-01-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Formula to compare a sum of a range to a cell value, and return cell count of range us

    hi...

    i use udf for this simulation,

    but, if you want to use excel formula,
    add row of accumulated demand as helper may be works.

    please check.

    regards,
    JRD.
    Attached Files Attached Files

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Formula to compare a sum of a range to a cell value, and return cell count of range us

    Hi,

    I have a single-cell solution to be copied across, though I don't understand your expected results for 20-Jul onwards, which are all 6.

    Can you please clarify?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Registered User
    Join Date
    06-03-2014
    Posts
    3

    Re: Formula to compare a sum of a range to a cell value, and return cell count of range us

    Hi thanks everyone - I am still working through the ideas on this given so far.

    Alastair - funny you should say that in your last paragraph on the estimation bit. That is actually how we currently have it set up to show Weeks Covered and my superiors are asking for a more intricate view

    XOR LX, the 20-Jul onwards, I simply put in a 6 if the total remaining demand showing (currently through 17-Aug) was covered by that week's on hand. The reasoning behind this is that 6 is the trigger for my group to begin investigating, so as long as we are covered through the end of this rolling period, I considered it "good" - I may be better served differentiating with a (6+) as opposed to a straight (6). I am kind of at a loss as to how to show those last few weeks, since this will be a 12 week rolling view and inevitably the final weeks will be creating errors and/or invalid due to the proceeding weeks not being visible.

    I would love a single cell solution, though I am trying out the macro and added row suggestions. I appreciate everyone's help - you all are making me think about this in a different way, which is helpful in and of itself
    Last edited by kerri2000; 06-05-2014 at 02:47 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Formula to compare a sum of a range to a cell value, and return cell count of range us

    Hi Kerri2000

    You can now demonstrate to your superiors that your the estimation method gives a an answer that is (a) easy to use and understand (b) for all practical purposes sufficiently close to the figure based on the forecast demand figure. Anyway - how accurate is the demand figure?

    Regards
    Alastair

+ 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. Compare cell range, with a value return
    By smac1011 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-19-2014, 10:18 PM
  2. [SOLVED] Formula to count frequency of data in cell range based on data in different cell range
    By jrosko1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 09:47 AM
  3. Replies: 1
    Last Post: 09-04-2013, 07:23 AM
  4. [SOLVED] Compare value of cell to a range of cells, then return the value of a different cell
    By azucar360 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-01-2013, 10:31 PM
  5. [SOLVED] create formula to compare cell 1 with a range of cell
    By KKXC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2006, 04:10 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