If Formula with multiple conditions

[rajkenya1]

Hi Guys


I need some help on a formulae which has many conditions.


I will try to articulate what I am trying to get in the best way possible


In the attached spreadsheet i have in row 9 a transition house which has number of clients and in row 20 the number of shifts.

The maximum number allowed in a house are 2 clients which will equate to 2 shifts maximum as per cell D20. If the number becomes 3 than the shifts will become 4. So i.e whether there is 1 client or 2 the maximum shifts are 2. As soon as an odd number person is introduced the shift moves to double. So for example if the clients are 7 then shifts should be 8 but using cell D20 to determine the multiples.

In the attached spreadsheet I have manually inserted in the blue highlighted area (cells E20 to N20) what the result should be.


I hope I have explained myself in a way where a genius from Chandoo can help.


Appreciate it.

 

[NARAYANK991]

Hi ,

I don't think you have explained the following :

using cell D20 to determine the multiples.

What will be the results if D20 contains 3 , 4 , 5 and any other value that it can have ?

Narayan

 

[rajkenya1]

Hi Narayan

Thanks for raising the question.

I think for the moment we can work with only 2 shifts in D20. I am not sure if a formula can be developed which can offer such a flexibility.

If possible please help otherwise work with just 2.

Thanks and regards

Rajeev

 

[NARAYANK991]

Hi Rajeev ,

See if this works :

=IF(E9=0,0,IF(ISODD(E9),E9+1,E9))

Enter this in E20 and copy across.

Narayan

 

[rajkenya1]

Dear Narayan

Thanks very much for a genius solution to this.

I think it first the purpose perfectly.

Sometimes simple formulas are the way.

Thanks so much