How do I model user calculations from Excel?

Question

How do I model user calculations from Excel?

Jez-448386

SSCarpal Tunnel

Points: 4594
More actions
January 23, 2014 at 3:20 am

#288636

Hi,
I am trying to build an automated process that is currently Excel based.
I will have a number of different metrics and the difficulty is the way in which the users use these metrics to produce the final numbers they want. I can hard code these different types of calculations but there must be better way.
These are some examples of the different types of calculations the users perform in Excel:
1. metric A minus metric B
2. the larger of metric C and of metric D
3. the larger of metric C and of (the sum of metric E and F)
4. (the larger of metric C and of metric G) plus metric H
and so on.
Currently, I have about 10 different metrics and 6 or 7 different types of calculations that I need to perform to calculate the final number and I could hard code the specific calculations. However, the range of metrics and the types of calculations will grow over time and I want to build a data drive solution and not one that requires a change every time there is a new metric or a new calculation method.
Has anyone got any ideas or a link that might help me?
Thanks
Jez

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's hard to think of a solution without some sample data and expected results. everything is possible, you just need to provide more information. Check the article on my signature to get better answers and I'll be happy to help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 2

declare @T table (

Scenario char(1)

,Metric char(1)

,Value int)

insert @T

values ('1','A',100)

,('1','B',80)

,('2','C',100)

,('2','D',80)

,('3','C',100)

,('3','E',70)

,('3','F',40)

,('4','C',100)

,('4','G',70)

,('4','H',40)

select* from@t

The expected results are:

1. 20 (being A-B)

2. 100 (as C is larger than D)

3. 110 (as the sum of E and F is bigger than C)

4. 140 (as C is larger than G plus H)

Jez

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'm not sure how would you automatize your process as you haven't given a source for your calculations.

I can get to your expected results but I'm not sure that would help you.

SELECT Scenario,

COALESCE(

MAX( CASE WHEN Scenario = '1' AND Metric = 'A' THEN Value END)

- MAX( CASE WHEN Scenario = '1' AND Metric = 'B' THEN Value END),

MAX( CASE WHEN Scenario = '2' THEN Value END),

CASE WHEN SUM( CASE WHEN Scenario = '3' AND Metric IN( 'E', 'F') THEN Value END) >

MAX( CASE WHEN Scenario = '3' AND Metric = 'C' THEN Value END)

THEN SUM( CASE WHEN Scenario = '3' AND Metric IN( 'E', 'F') THEN Value END)

ELSE MAX( CASE WHEN Scenario = '3' AND Metric = 'C' THEN Value END) END,

MAX( CASE WHEN Scenario = '4' AND Metric IN( 'C', 'G') THEN Value END)

+ MAX( CASE WHEN Scenario = '4' AND Metric = 'H' THEN Value END)

)

from@t

GROUP BY Scenario

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 4

Thanks Luis.

The source for the calculations would be a user front end so that they can define new 'scenarios'. For example they could define a new scenario that is A+B or one that is the larger of A and B.

I have done some more thinking on this and I think that this will be a 'rules engine' but my impression is that these tend to be complex but this seems a 'very simple' requirement.

Jez