Mar 16 2022 09:09 AM
Good Evening
I need some assistance. I have a column with numbers and need to do the following calculation:
IF b1 > 100 allocate 3 and for every additional 150 beds i must add 1 to the 3. If it is less than 100 it must allocate 0. For example if you have 353 beds you will allocate 4. Not sure if I make sense.
Your assistance will be highly appreciated
BASE 1 | 353 |
BASE 2 | 1100 |
BASE 3 | 412 |
BASE 4 | 93 |
BASE 5 | 209 |
Mar 16 2022 09:22 AM
SolutionHello @Naaz2006
Maybe like this;
If less than 100, use zero. Otherwise, 3 plus one for each the number above 100 that is fully 150.
=IF(B2:B11<100;0;3+INT((B2:B11-100)/150))
Mar 16 2022 01:06 PM
@bosinander , Thank you. It is working . Your assistance is highly appreciated
Mar 17 2022 12:13 AM
You're welcome :) And, as you can see, @Riny_van_Eekelen shows another way to solve it. Understanding different approaches enables more possibilities to solve future needs.
Mar 19 2022 04:34 AM
Mar 16 2022 09:22 AM
SolutionHello @Naaz2006
Maybe like this;
If less than 100, use zero. Otherwise, 3 plus one for each the number above 100 that is fully 150.
=IF(B2:B11<100;0;3+INT((B2:B11-100)/150))