combining formulas

[bubs]

Hi,

I want to combine the two formulas below, how do I do it? I can only get it to display true or false and not the value I want.

=IF(OR((B3)={7,6,5,4,3,2,1}),CHOOSE(B3,7,6,5,4,3,2,1))

=IF(OR((A2-B2+B3)={1,2,3,4,5,6,7,8}),CHOOSE((A2-B2+B3),1,2,3,4,5,6,7,8))

[TMS]

Not entirely sure how you want to combine them. Problem is, neither formula has a FALSE result.

So, you could change them to:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

Then you could replace the "other" (the FALSE part) of the first formula with the second formula. Thus:

Please Login or Register  to view this content.

Or something like that. But I don't know how exactly you want to combine them or what the outcome should be. In this example, if B3 was 0, you'd get the answer = 2.


Regards, TMS

[bubs]

I have the criteria below - what I am trying to figure out is the starts required:

if current minus leavers is more than capacity = 0
if leavers is more than 8 = 0
if leavers is 7,6,5,4,3,2,1 put 1,2,3,4,5,6,7
if starts required is 1,2,3,4,5,6,7,8, put 1,2,3,4,5,6,7,8
if starts required is more than 8 = 8

I think in order to do this I need to combine the two red highlighted conditions but I cannot seem to make it work. These are the individual formula I have created for each condition:

=IF((B2-B3)>=A2,0)
=IF(B3>=8,0)
=IF(OR((B3)={7,6,5,4,3,2,1}),CHOOSE(B3,7,6,5,4,3,2,1),0)
=IF(OR((A2-B2+B3)={1,2,3,4,5,6,7,8}),CHOOSE((A2-B2+B3),1,2,3,4,5,6,7,8))
=IF((A2-B2+B3)>=8,8)

[TMS]

In all those formulae, you only have a positive/TRUE outcome. So, if the logic sequence is as listed above, you should tag each formula onto the next.

Please Login or Register  to view this content.

Give or take


Regards, TMS

[bubs]

TMS, It has taken me 3 days to get to this point.

I had been trying to do it as a complete formula up until today. I only seperated each formula to try to figure it all out this morning, how ridiculous is it that I didnt just then try putting them all together again after I had changed the order.

Thank-you for your help, slightly worrying that I needed you to point out something so obvious! So frustrating!!! Thank-you so much

EDITED: - I just realised it still isnt working The number of starts cannot put the current over the capacity.

[TMS]

You're welcome. Thanks for the rep.


It's often easier to look at each of the conditions individually and test them out. You can put them in separate cells, maybe on the same row, and see if you get the answer that you expect for that condition. It's important to remember the FALSE part though, especially while you are testing. I often write, for example:

Please Login or Register  to view this content.

The "x" part is just there as a place saver while I work out what it needs to be. It will become the next IF, or whatever. And finally, wrap it all up in an IFERROR function to cater for the situation you didn't consider. Maybe:

Please Login or Register  to view this content.

Another thing to consider is putting the most likely outcome(s) first. It won't make a huge difference in one off calculations but, in complex formulae over thousands of rows, it's better to have the most likely outcome tested first.


Regards, TMS

[bubs]

It still isnt working unfortunately. The number of starts cannot put the current over the capacity. I need to add something else to factor this possibility out. Back to the drawing board!

[TMS]

Start with the basics. You have a capacity of 55, a current staffing level of 53 and planned leavers of 5. So, potential starts is

Please Login or Register  to view this content.

So where do you need to go from there?

Regards, TMS

[TMS]

See example

[bubs]

It worked in the main, but when I got to someone with a lower capacity it started to go wrong. I think I may need to change the order or something but am feeling a bit lost now!

[TMS]

OK, you're going to need to explain the "rules" to me. You have a capacity, you have a current staffing level, and you have some planned leavers. So, logically, you could start ... what I said before:

Please Login or Register  to view this content.

But you're not; it's more complex that that, I guess. But your table only lists target starts, not actual starts. The top row should be previous current - previous leavers + current actual starts. But it's just static numbers.

Again, see the example.

[bubs]

I do work out the next months current by "previous current - previous leavers + previous target starts". They display as static numbers as I had to remove some formula due to it referencing some data I cannot attach.

Would I need to create another row for potential starts in order to make the new formula work? Or could I set the limit of target starts = capacity as a maximum? This is a bit more in depth than I have tried before and my brain seems to struggle to compute it all!

[TMS]

I think it depends on how you want to track it, and how difficult and opaque the calculations. I'd say you need another row to record actual starts. That would influence potential starts for the following period which, in turn, should be a maximum for the target.

I don't know, it's your staffing levels. For example, just because you could recruit 10 or 20 people, say, it doesn't mean you should as there is an overhead on induction and training and monitoring and ... and ... So, that's another factor to complicate things.

Anyway, see the example.

[bubs]

I believe I set the limit of the Leave & Start number to not total more than 8 by using the choose functions in the formula. That is how I limit there being too much work for any one person each month.

I dont need to seperately record actual starts. I wont be keeping old data so once Nov has passed the Dec current will be an actual number so I'm not too worried about keeping track of how many starts there were as this will be done within the data it is referenced from.

I just need to stop the target start number making next months current more than capacity. I think I can add to the formula you created earlier (below) but I dont know where to add it in the sequence, or indeed what the additional section would be. Ive tried something along the lines of the highlighted red below but it doesnt quite work and I think I'm making it more complicated than it needs to be.

=IF(C3-C4>=A3,0,IF(C4>=8,0,IF(OR(C4={7,6,5,4,3,2,1}),CHOOSE(C4,7,6,5,4,3,2,1),IF(OR(A3-C3+C4={1,2,3,4,5,6,7,8}),CHOOSE(A3-C3+C4,1,2,3,4,5,6,7,8),IF(A3-C3+C4>=8,8,0)))))

=IF((Y2>=(X2-7)),(X2-(Y2-Y3)))

[TMS]

OK, try this:

Please Login or Register  to view this content.

I can't believe it needs to be that long but it seems to work and incorporates all your sub-formulae.

Regards, TMS

[bubs]

Seriously, you are my actual hero. Thank-you so much. I doubt if I ever would have been able to come up with the answer on my own. You area genius