Formula based on a column value

[DGK1981]

I am trying to create a workflow that will multiply a value based on another field and corresponding year entry: I have the following data/columns (-Sales Year -Sales volume -Warranty End period -Failure rates for each year). In the example attached i want to multiply the sales volume by the corresponding failure rates based on the warranty period range. Comments in the attached file. If this can be done, it would save a huge amount of time moving :). Many thanks. David.

[danilang]

Hi @DGK1981 

Because of the varying columns this looks like a case for a Transpose/Crosstab solution.  Can you provide some sample data?

Dan

[JamesMA · Accepted solution]

Hi,

This can be achieved using a formula tool as shown below. I have created a workflow with a simple and a more dynamic option that will match the warranty end year to the appropriate return rate and only calculate expected sales volumes for the appropriate year.

Let me know how you get on!

[JamesMA]

@DGK1981 

Tagging this so it's easy to find for you :)

[jnans]

I am posting a temporary solution for you that will work. I am hoping someone will post a more dynamic solution for this as I would now also like to know a more dynamic approach to solving this. This one would at least get you started if you needed to do this on a dataset ASAP but not ideal as you would need to keep changing the parameters whenever the years and data change a bit from what you originally have listed.

[jnans]

@JamesMA  Your Solution would work for the most part but I believe the formula would be IF [Warrant End Period] >= [Name] THEN action it. This will then calculate both all years that was wanted in the original problem. I attempted your solution with a dataset of 4 records so I could see if I could get the same results in a larger set.

[DGK1981]

Thanks James, this worked perfectly!

David.

[JamesMA · Accepted solution]

@jnans great spot! 

I hadn't thought of that!

It makes it much more dynamic.

@DGK1981 I have attached here an improved version after the advice from @jnans 

Let me know what you think :)

[sangram1]

So very nice article