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)

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.




@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.


As variant

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