Dec 03 2022 01:52 PM
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
Dec 03 2022 02:19 PM
SolutionThat 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) )
Dec 03 2022 02:40 PM
Dec 03 2022 02:19 PM
SolutionThat 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) )