SOLVED

Assistance with nested IF

New Contributor

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 1353
BASE 21100
BASE 3412
BASE 493
BASE 5209
5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

Hello @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))

bosinander_0-1647447574624.png

 

@Naaz2006 As a variant.

 

Screenshot 2022-03-16 at 17.29.51.png

@bosinander , Thank you. It is working . Your assistance is highly appreciated 

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.

@Naaz2006 

As variant

=IFNA( XMATCH(B2:B11,SEQUENCE(10,,100,150),-1)+2, 0 )

 

image.png