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 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
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.
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
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.
Forgive my stupidity but do I add the If formula to the sub Product formula and how do I do that
Barry
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
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
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
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
Thanks to all I was able to fix the problem.
Barry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks