Total until criteria is met

[dave1983]

Hi There

I am looking for a formula that will use the following factors to total until criteria is met

I have time intervals in A2:A61
Corresponding results in column B2:B61

My criteria total is in O3 (I also have a second criteria in F2)

In column O6 downwards I want each result from A1:A60 to fill in until the total in O66 gets within the creteria in O3,I would also only like the results to show after time interval criteria in F2 (so for example if F2 has 20 seconds i want it to look at the table in A2:A61 and return any result form B2:B61 that are 20 and above to total until criteria in O3 is met/nearly met), it will more than likely never get the right number but if possible I don’t want it to exceed the criteria.

I have tried vlookups, subtotals, ifs formula as well as looking online for a similar question but to no avail.

I have included an example of my sheet if it helps.

Any help would be appreciated.Attachment 411627

[cyiangou]

Must each interval be selected only once? Or can we select say 5 of interval 16 + 3 of int. 52 ?

[dave1983]

yes I only want to select each interval once.

[cyiangou]

But then surely the best solution is the one that selects 1 of every interval that is above the message time threshhold?

[dave1983]

but then the total would exceed the criteria in O3.

In the example the interval data totals to 8289, I'm only interested in seeing how many intervals after the message time in column F2 it takes to get close to the figure in O3 (so not all intervals will need to be used)

I know you could just higlight and drag down and use the auto sum but would prefer the formula to do the work.

[Jacc]

Put this in O6 and copy down:

Please Login or Register  to view this content.

[dave1983]

Put this in O6 and copy down:

Please Login or Register  to view this content.

You are a star that is pretty much exactly what I want but it ends up totalling to higher than the criteria in O3 and I only need the results from the closet interval (which the criteria is in F3) onwards, could it be tweaked so it wont exceed the number and only include those intervals.

to confirm in the example I only need intervals from 16 onwards, but going forward the intervals i would be interested in will change

[cyiangou]

Never mind, I'm an idiot. I applied the 90% constraint to the resulting message time, not the target message time. D'oh.

Ok, I've configured this as a solver model, so the results will be optimal, ie. you can't find a better solution given the current constraint set.

Sorry for reformatting, that was therapeutic for my OCD.

call_solver_cy.xlsm

Edit: I'm pretty sure that this is the 'Knapsack' problem.

[dave1983]

Never mind, I'm an idiot. I applied the 90% constraint to the resulting message time, not the target message time. D'oh.

Ok, I've configured this as a solver model, so the results will be optimal, ie. you can't find a better solution given the current constraint set.

Sorry for reformatting, that was therapeutic for my OCD.

Attachment 411651

Edit: I'm pretty sure that this is the 'Knapsack' problem.

this looks good, just popping out so will look when I get back and can mark solved hopefully

[Jacc]

that is pretty much exactly what I want but it ends up totalling to higher than the criteria in O3 and I only need the results from the closet interval (which the criteria is in F3) onwards

I have no idea what you mean. What's a closet intervall?

"which the criteria is in F3"
Sorry I don't understand this sentence. Also I don't see anything in F3.

to confirm in the example I only need intervals from 16 onwards, but going forward the intervals i would be interested in will change

16 what? Row 16? Or data no 16?

[cyiangou]

Yikes, I've just run the model off the uploaded version, and it takes a while to find a solution the first time.

If it takes too long for you, escape, and then go to Solver, Options and set Precision to a lower value, say 0.0001.

[dave1983]

right I have had a look at this and its definitely a step in the right direction but a couple of things I want to check

just to confirm and apologies if I hadn't made it clear but in your example there are intervals missed out within the range but I don't want them missed out as I want every range included that is above the message time parameter in D5 but stop only stop including the interval results when the total does not exceed C7

so from your sheet all results from 20 (C9) to 290 (C62) totaling 7406, if you include the next cell down the total would be 7483 so that is to much.

Your formula in D10 down has

Please Login or Register  to view this content.

but should this not reference the intervals in B10 down so be this?

Please Login or Register  to view this content.

what does the interval selected column do? and when i click solve I get the error compile error cant find project or library

[Alf]

Hi cyiangou,

I almost feel like I'm nagging but have you tried to replace the constraints

"$E$10:$E$69 = Integer" and "$E$10:$E$69 <= 1" with just "$E$10:$E$69 = binary" and set up you model as "Assume Linear Model"?

Alf

[Alf]

Hi Dave

You need to set a reference to Solver in Visual Basic

Click Visual Basic icon, select tool -> References and tick box marked "Solver"

Alf

[cyiangou]

Hi Alf

Thanks, I'll try these suggestions, but I suspect a linear model won't work because of the non linear relationship between the intervals and their corresponding message times.

...and suggestions are always welcome.

[dave1983]

Hi Alf

is there a way to do this without the use of a macro?

also I dont have solver to choose from in the references box as per screen shotExample.jpg

[cyiangou]

what does the interval selected column do?

These are your results: A '1' means that that interval was selected to make up the total. A zero means that interval was dropped from the solution. The total in E7 tells us that 46 of the 60 intervals are included in your result 'basket'.

there are intervals missed out

Just fill them in (they were empty in your file), they should get treated appropriately

but should this not reference the intervals in B10 down so be this?

No, this is correct. This formula tests whether each interval's message time is greater than the product's minimum message time.

[cyiangou]

is there a way to do this without the use of a macro?

Yes, you can just dump the 1 line of macro code, save as xlsx, and then run the Solver from the Solver button on the toolbar. I misremembered someone asking for "all this with just a button press", and thinking it was you.

[dave1983]

sorry i am getting a bit confused now

Alf my original example had all the data in cells A2:B61 I cant see any missing data apprt from intervals that had no data anyway.

cyiangou I dont really understand what you mean as I cant see a solver button on my toolbar just the solve button in the sheet itself.

[cyiangou]

I cant see a solver button on my toolbar

Glad you're on 2007. Go to the Data tab/ribbon, far right, on the Analysis panel, there is a solver button. Here's also where you see the Solver parameters and constraints for the model.

You may have to go back to your Office setup, and install the Solver. I think it may not be installed by default. Well worth doing though.

[dave1983]

will have a look

[Alf]

non linear relationship between the intervals and their corresponding message times.

Yes there is the "IF" formula in column D but after changing two constarints to one, ticking "Assume Linear Model" and unticking "Use Automatic Scaling"
solver will pop up with a solution but complains about "the conditions for Assume Linear Model are not satisfied".

Selecting "Keep Solver solution" and clicking "OK" will show that solver found an optimal solution picking 29 intervals with a total duration of 7460.

Same behavour for solver excel 2003, excel 2010 goes in a loop but pressig "Esc" button after ca 5 - 10 seconds and selecting "Stop" will give you the same result.

The best behaved solver model is the OpenSolver, no complaints or arguments just gives a result at once 43 intervals with a total duration of 7460.

Alf

[Jacc]

cyiangou, you haven't unhooked the "Ignore integer constraints" under Options.
Also, your column with 1 and 0 is formatted with no decimals so it looks like it's all 1 and 0 when it's really decimal numbers so even though it finds a solution it's not correct at all.

[Jacc]

Here i implemented some of Alf's suggestions. Binary as a constraint, no Automatic scaling and no "Ignore integer constraint". I also set the timelimit to 20 seconds instead of hitting esc.

Alf, why does this loop thing happen? It seems like there is a solution ready in a few seconds, yet it keeps searching.

[dave1983]

thanks for this you have both both been amazing, maybe I am going about this the wrong way, my ultimate goal is to see how many intervals after the Message time parameter it takes to answer 90% of the total in C7, no intervals can be missed out up to that point so on this one all intervals from 16 - 290 will be 7406 so the figure i want is 274 the difference between the first interval and the last.

hope this makes sense and really sorry if I have wasted anyone's time!!!

[Jacc]

Things are starting to clear for me now. Is this in the right direction?

[dave1983]

Things are starting to clear for me now. Is this in the right direction?

ahh yes this looks absolutely perfect for what I need to do, thanks for this you are a star.

[dave1983]

one thing I notice is that it takes it from the first cell with an number that also an interval higher than D5, I would idealy like the difference calculation to include that cell in even though there is no figure, Ill explain why I want this

the interval column is a count in seconds

as soon as someone phones us the seconds start to count up, they will be presented with an automated regulatory message and then given relevant options to direct there call to the right place.

once they have gone through the menus and chosen there option and an agent physically picks up the phone the number closest to the second is incremented by 1, so on and so forth

so as per the example 3607 people phoned listened to options etc and got through to an agent after 22 seconds.

we have several different lines options for them to choose hence the helper column to choose relevant line.

so I want to include the interval that is closest to the message time even if no calls were answered at that point as it is still a period of time that has elapsed that conts towards the overall figure of how many seconds it takes to answer 90% of the figure in C5

[Jacc]

I still don't follow your explanations very well. Here is the tip of the century:
Instead of putting effort into explaining, put solved cases in your workbook. In other words, put examples of data in your workbook AND your expected results. You know what they say, a picture says more than a thousand words. In case of Excel it's more like: 3 solved examples in a workbook says more then 100 forum posts with 200 words each.
That being said, my brain is in a poor working condition today, I usually get things slightly faster. This rounding off stuff that you ask for now I suck at even on the best of days. Maybe my wild goose chase partners Alf and cyiangou can help out here?

[cyiangou]

Jacc,

Also, your column with 1 and 0 is formatted with no decimals so it looks like it's all 1 and 0 when it's really decimal numbers so even though it finds a solution it's not correct at all.

When I was still tuning the original model, I reduced the solver precision to get faster results. I was happy that I had constrained the model for integer results, but I got fed up with seeing 2 shown as 1.99999999, so I formatted that away. But I would hardly call 4.999999995 in lieu of 5 "not correct at all"

[Jacc]

Ok, gotcha.

[cyiangou]

you haven't unhooked the "Ignore integer constraints"

I don't see this option in 2007.

[Jacc]

Looks like this in 2010 Edit: Probably 2013 I got
Ignore.PNG

[dave1983]

Hi Jacc

I have put a lot of your formula examples into my sheet and I am pretty much there, its not pretty but I will sort that when it all works, the last interval check works fine but the first interval should be picking up from the time closest to the message time parameter, so as you will see in my example all intervals regardless of whether they have a figure or not are selected until the desired 90% figure is reached but the first interval seems to think the first interval is 20 and not 16 as it should be.

basically the first interval needs to select the figure >= message time, so 16 needs to be the first interval as per the example.

I hope this makes sense

[cyiangou]

Jacc,

Quite primitive in 2007:

solver2.JPG

[cyiangou]

Dave,

Ok, after reading your last few business requirements, I think I now understand.

Not a solver model at all, although it can be used.

Is this actually what you're looking for?

call_not_solver_cy.xlsx

(we're looking for the cumulative area under the s-curve of your probability distribution, and finding the 90% mark)

[cyiangou]

And Dave, remember that you hit your 100 posts mark here.

[Jacc]

Trying again.

Sorry if I upset you cyiangou, it was not my meaning. We all went for a little loop there, some good examples would have saved us all some time.

[cyiangou]

Not upset at all. What gave you that idea?

[Jacc]

Oh, I thought that 100 comment was sarcastic towards me. But now that I know that it's not let's go right over to dave's desk and encircle his chair, put our arms across our chests, put on a mean frown and stare him right in the eyes. DAVE! We are not kidding when we say that you should focus on putting solved examples in your workbook rather than write long posts!
It seems we are close to the finish now but you remember that to next time!

[cyiangou]

Haha! Yes, half the challenge was decoding the OP.

[Jacc]

On the other hand, he hinted that my first suggested solution was pretty close... then someone went all Solver and then Alf showed up, pouring gasoline on the fire. I think everyone should be punished. https://www.youtube.com/watch?v=7O6Rm4qNILA

[dave1983]

Hi Everyone

it looks like we are where we need to be, thanks to every one on this journey

[Jacc]

'''''''''''