Any Excel equivalent for Match function with Match type 1 or -1?

we have Excel Match formulas which looks thru a lookup_array

1) which finds the largest value that is less than or equal to lookup value (match type 1) and

2) which finds the smallest value that is greater than or equal to the lookup value (match type -1)

Eg. of Usage : Supposing we have tank table for liquid A as below. Need to know TankCateg for smallest tank which will hold a 150K gallons liquid A. Using match type -1 in Excel Match function, the tankcateg is 3, capacity 200,000.

Liquid Type A Tanks:

TankCateg Capacity(gals)

1 300,000

2 250,000

3 200,000

4 100,000

5 50,000

6 10,000

We have tanks sizes and category in a table as there are many tank categories and liquid type.

How do we do this in Anaplan?

Thank you,

Cheng

Accepted answers

All comments

LipChean_Soh

The formula for "Capacity Tank" is shown above.

You'll need to select "Last non blank" for Summary method of "Capacity Tank".

Formula for "Final Tank" is just "Capacity Tank"

Tank_24529.png

PaulRitner

However this is accomplished, it needs to scale. Specifically, if the number of Liquid A transactions grows (potentially into the millions & up), the method of mapping each transaction to the standard tank size needs to remain relatively constant. (Combining Transactions with tank sizes in a single module may not scale well if there are a lot of standard tanks & a lot of transactions)

One way to do this (in a way that will scale) is to (dynamically) map Liquid A transactions to a managable map.. that will, in turn, get us to our standard tank.

The first step would be to determine the standard tank mapping increments. In the example below, I created a simple list that assumes a 10,000 gallon progression and placed the increments under the parent we want them to map to. For example, a 40,000 gallon interval maps into the 50,000 gallon standard tank size.

Second Step: Since the Liquid A transactions might be all over the place (even down to 1 gallon... or even in ounces, who knows.. we would need to ROUND each transaction UP to the nearest mapping level... we can do that with the round function as "ROUND(Liquid A Transaction Amount/10000,0,UP)*10000"... to align each transaction with the corrsponding mapping interval.

The two images below show:

1. a sample mapping list (should be slow moving since we are dealing with 'standard' tank sizes)

2. a module to show how transactions can quickly be mapped to the standard tank size using a couple line items to: round the transaction value to the nearest interval, use a finditem to find the mapping value in our list, and then grab the mapping item's parent, which is our standard tank size.

There are probably lots of ways to skin this cat... the key is to ensure the approach will scale (and be easy enough to administer)

Cheers!

Standard Tank Map.png

Standard Tank Map Line Items.png

cbeh

Hi Paul,

This is very helpful.

Thank you!

-Cheng

cbeh

Hi LipChean,

Thank you! I will test and see if the calculation time is workable with the transactions, tank sizes and liquid types that I have.

-Cheng

LipChean_Soh

Hi Cheng,

I forgot to mention that the Line Item "Capacity Tank" in my example uses 2 Lists, i.e. Entity and Tank.

Thanks

Quick Links

Can you help?

Unanswered questions

Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…
Curious Comparison: Forecast Modeling for Unusual Products
I've been experimenting with some forecast modeling techniques in Anaplan recently and decided to test them on a few quirky, offbeat product categories just for fun. One of the categories I played around with was love dolls—yes, you read that right. It actually turned out to be more interesting than expected. Unlike…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled