+ Reply to Thread
Results 1 to 10 of 10

length Calculation

  1. #1
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371

    length Calculation

    Attached is the zip file with the detail of what I am hoping to achieve which is calculating the number of lengths required from generated infromation.
    Thanks in anticipation
    Barry
    Attached Files Attached Files

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    I think this might be what you're looking for:
    =SUMPRODUCT((C8:C45)*(D8:D45))/5400

    Based on your example, it says you should order 33.79 lengths.

  3. #3
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    Paul, appreciate the help and it works fine.
    Only problem is that, in my actual sheet I have a number of #NUM! errors which are the result of the array formula
    My question is how can I use the full range but eliminate the #NUM! errors
    I hope I am not starting a new thread here
    Barry

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    If your sheet has a #NUM error, is that equivalent to 0? If so, are you able to simply use an IF statement, along the lines of:

    =IF(ISERROR(your_array_formula),0,your_array_formula)

    Then this SUMPRODUCT will record a 0 for that row when it's adding up the totals.

  5. #5
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    Forgive my stupidity but do I add the If formula to the sub Product formula and how do I do that

    Barry

  6. #6
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    The Sum Product formula just adds the total lengths then divides the total into the available size [eg 5400] which does not really give the desired result.
    Perhaps there might be a complicated formular which looks at each required length then does the work
    e.g. - 2700 x 2 lengths - 1 requirement for a 5400
    Is is possible for a formula to look at each requirement versus how many required
    and I still cannot work out how to clear the #NUM! errors and where I put the formula suggested.
    regards

    Barry

  7. #7
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    Barry,

    To clear the errors do this using PJ's formula

    if(iserror(SUMPRODUCT((C8:C45)*(D8:D45))/5400),"",SUMPRODUCT((C8:C45)*(D8:D45))/5400)

    This works on the principle of evaluate the formula, if it gives an error, return blank - otherwise return the calculated value.

    Ed

  8. #8
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    Barry,

    I have had a think about this and what you are asking is really very complicated.

    It requires starting with the longest desired length and cutting this. The remainder (offcut) then may be usable for cutting another length or it may be waste. The problem arises how to match the remainders against the outstanding lengths required.

    I am sure that it is possible to work out an algorithm for this but it would take more time than I have available.

    Sorry I can't help more

    Ed

  9. #9
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    Thought it would be a little complicated but thanks anyway appreciate the help.
    Just getting back to the "If" formula - where do I place it and how do I do it.


    Barry

  10. #10
    Forum Contributor
    Join Date
    03-09-2007
    Location
    Australia
    MS-Off Ver
    Office 10
    Posts
    371
    Thanks to all I was able to fix the problem.
    Barry

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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