cancel
Showing results for 
Search instead for 
Did you mean: 

SAP IBP Formula similar to excel CEILING function

AndreiLupu
Explorer
0 Kudos
252

Hi all,

I have been searching for answers on this one, but couldn't find any. Perhaps i will strike some luck here.

I need to calculate a CEILING between two different inputs, for example a KF and an integer attribute:

KF_A: 100  

Attribute_B: 15 -> The multiple to which KF_A will be rounded

-> The formula should result in 105 which is the KF_A rounded up to the nearest multiple of Attribute B

There is CEIL IBP formula, but it only allows for one input, therefore not usable in this case..

There is an MS Excel formula that works perfectly: https://support.microsoft.com/en-au/Key Figure Formulaoffice/ceiling-function-0a5cd7c8-0720-4f0a-bd2... 

 

Any help will be greatly appreciated.

Thanks! Andrei

 

View Entire Topic
riyazahmed_ca2
Contributor

Hi @AndreiLupu,

I derived the below procedure and it works well for me. 

=IF(ATTR_B = 0, KF_A, IF(KF_A/ATTR_B > 0, ROUNDUP(KF_A/ATTR_B ,0), ROUNDDOWN(KF_A/ATTR_B ,0)) * ATTR_B)

Try at your side and let me know

Best Regards,

Riyaz

 

AndreiLupu
Explorer
0 Kudos
Thank you for replying @riyazahmed_ca2. In the meantime I was able to work it out using the formula i mentioned in my first reply (IF(KF_A=0,0,IF(KF_A<=Attribute_B, Attribute_B,CEIL(KF_A/Attribute_B)*"Attribute_B"))
riyazahmed_ca2
Contributor

@AndreiLupuI think your calculation won't work as Excel CEILING function especially if the KF_A is negative and ATTR_B is a positive value. Refer the Excel CEILING function Help and check whether the below cases works out in IBP.

riyazahmed_ca2_0-1718865529343.png

Regards,

Riyaz