SOLVED

New Contributor

# Assistance with nested IF

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
5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Assistance with nested IF

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))``

# Re: Assistance with nested IF

@Naaz2006 As a variant.

# Re: Assistance with nested IF

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

# Re: Assistance with nested IF

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.

# Re: Assistance with nested IF

As variant

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