How to create a current value by multiplying the value before it with another value

Hi there,

I am trying to convert formulas from excel into Anaplan but am struggling with the formula in Anaplan.

The pictures i have uploaded show the type of formula i want to create. I guess what i want to know is how can you be specific about the exact cells you use in Anaplan.

Any help would be greatly appreciated.

Screenshot (69).png Screenshot (71).png

Screenshot_(69)_116524.png

Screenshot_(71)_116524.png

Accepted answers

All comments

ashish.banka

@sophiedyason You need to leverage Anaplan functions to achieve these (You can try Lag, Offset etc)

Please refer the functions available in Anaplan : https://help.anaplan.com/186d3858-241b-4b78-8aa5-006fc4260546-All-Functions

riyazpasha

Hi @sophiedyason ,

Create two line items which are I guess input and in the final value line item write the below formula.

Base - Base Input - (OFFSET(Base, 1, 0) - OFFSET(Base Input, 1, 0))

Thanks,

Riyaz

KirillKuznetsov

Hi @sophiedyason ,

Riyaz gave a really good solution. From me I would add one more thing:
Based on preformance comparison of the time formulas (previous/next, lag/lead, offset, lookup) for your use case I would recommend to use NEXT() rather than OFFESET(). More you can learn here: Performance Comparison - OFFSET/LAG/PREVIOUS/LOOKUP

The difference in performance in your case is not significant between the either options, but formula looks a bit cleaner this way: Base - Base Input - (NEXT(Base) - NEXT(Base Input))

Kirill

ChrisAHeathcote

@sophiedyason

If you data is a time series and you are using native time dimension then use PREVIOUS and NEXT to reference data from the cell one period before and one period ahead of the target cell.

If your data is not time series and you need to reference data from various sections of another list then you will need to create a mapping table which will inform which item should be reference on each occasion. For this use LOOKUP .

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385