Apptio for All

A place for Apptio product users to learn, connect, share and grow together.

View Only

Mapptio Puzzle #3

Mon October 07, 2019 04:04 PM

Adam Moretz

To conquer the third location, help @Adam Moretz�, @Andrew Mulvaney and @Michael Darragh� modify the formula to correctly pull back the corresponding weight.

I received a file to use as a weighting table for my applications. The weighting table is based on the application family when it exists, and a default weighting for when an application family is not defined. I tried to use a lookup, but my weighting isn't always returning a value. How can I modify the formula below so that we’re correctly pulling back the corresponding weighting? Both data sets are attached.

This location closed October 31, 2019.

#ApptioforAll

Statistics

0 Favorited

1 Views

2 Files

0 Shares

2 Downloads

Attachment(s)

Download All

Applications List TBM Quiz.csv.zip 515 B 1 version
Uploaded - Tue October 29, 2024

Download

App Family Weighting TBM Quiz.csv.zip 342 B 1 version
Uploaded - Tue October 29, 2024

Download

Comments

Adam Moretz

Fri November 01, 2019 01:44 PM

Correct Answer:

=Lookup(App Family,App Family Weighting,App Family,Weighting,false,true,true)

Formula Breakdown:

=Lookup(source_column,lookup _table,matching_column,replacement_column[,leave_original_ value][,replace_nulls][,ignore_case])

There are two issues causing the weightings to not pull in correctly – 1) case sensitivity and 2) null values.

By listing the 6^th and 7^th parameters as true, you’re telling the formula to replace the blanks with the null value from your table, as well as telling it to ignore case sensitivity.

*Note - there were several other methods that would work, but the answer provided is the simplest way to get the desired outcome.

Points were awarded if your method was correct.

Winners:

@James Caldwell

@Kyle Castro

@Tony Wong

@Tobias Zimmerman

@Oliver Smith

@Douglas Mahan

@Raymond Walls

@Nicole Sangid

@Jaitabh Jewel Sharma