Forum Discussion

Ash7991's avatar
Ash7991
Copper Contributor
Dec 03, 2022
Solved

IF statements

Hi Everyone, I am attempting to use IF statements for the first time, and very typically for me - I have not started off easy! 

 

I'm attempting to say if Cell (F7 for example) = X, then round up to nearest Y

X = <30, Y = 2

X = >30, <100, Y = 5

X = >100, <200, Y = 10

 

So far (just to test) F7 is 20.76, and I have the following formula that returns a result of 22:
=IF(F7<30,CEILING(F7,2))

 

I'm just not sure if this is right, or where to go from here. 

 

Any help would be appreciated

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 03, 2022

    Ash7991 

    That could be like

    =CEILING(
    F7,
    IF( F7 < 30, 2,
    IF( F7 < 100, 5,
    IF( F7 < 200, 10, 1)
    ) ) )

    or

    =CEILING( F7, LOOKUP(F7, {0,30,100,200}, {2,5,10,1} ) )

    or

    =CEILING( F7, IFS( F7 < 30, 2, F7 < 100, 5, F7 < 200, 10, TRUE, 1) )

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 03, 2022

    Ash7991 

    That could be like

    =CEILING(
    F7,
    IF( F7 < 30, 2,
    IF( F7 < 100, 5,
    IF( F7 < 200, 10, 1)
    ) ) )

    or

    =CEILING( F7, LOOKUP(F7, {0,30,100,200}, {2,5,10,1} ) )

    or

    =CEILING( F7, IFS( F7 < 30, 2, F7 < 100, 5, F7 < 200, 10, TRUE, 1) )
    • Ash7991's avatar
      Ash7991
      Copper Contributor
      Dec 03, 2022
      Thank you so much! I've tried the first one and it does exactly as asked - hopefully I can learn from that!

Resources