Getting the % error when a variable in a balanced equation is changed

[okeane2]

I have a nitrogen mass balance (ins-outs=losses). I want to create a spreadsheet that will show the % error of the loss value when I change the one variable.

for example see inputs and outputs below:

a b c

1 "in values" "out values"

2 1 2

3 2 3

4 3 3


Then the equation is:

(1+2+3)+(-2-3-3) = -2


If i were to change one of the inputs

a

1 "inputs"

2 4

3 3

4 2


Then

(4+2+3)+(-2-3-3) = 2


I want the spreadsheet to tell me which variable was changed (value in a2) and by what percentage was the overall loss changed by the change of variable. This is a very simple example but I can't get my head around what needs to be done... I think I'll also want to have a graph showing how the change in the different inputs effects the loss.


I really have no idea if this makes sense to anyone but myself but if anyone can help I would greatly appreciate it! I feel like it is a type of regression modelling but do not know how to go about solving it.


Thanks.

 

[okeane2]

Sorry the formatting was lost but the inputs are 1,2,3 and outputs are 2,3,3

The second set of inputs are 2,2,3


Apologies.

 

[NARAYANK991]

Hi ,


I think the problem with understanding your problem is the loss of formatting ; can you specify the details in this format :


IN VALUES ( with the cells used ) :


A1 : 4

A2 : 2

A3 : 3


OUT VALUES ( with the cells used ) :


B1 : 2

B2 : 3

B3 : 3


OUTPUT RESULT ( with the cells used ) :


C1 : 2


Cell which will be changed : A2 from 2 to 3


If what you want is a comparison of before with after , then it is possible VBA may be required. Is this acceptable ?


Narayan

 

[okeane2]

Thanks Narayan, I really have no idea how to use VBA but I'll attempt to be clearer in my explanation... no worries if you don't have a solution as I'm pretty sure it's my question asking that is the problem here!


I have a balanced equation with three input value and three output values. I want to know by what percentage the solution will change if I change a single variable.


The background to this is that I am doing a nitrogen balance - I have input nitrogen in the chickens, the bedding, the feed and output nitrogen in the moralities, the chickens leaving and the manure (manure is the bedding+excretion). When I take the outputs from the inputs I should be left with the loss of Nitrogen.


I want to know which variable (input or output) affects the solution most - i.e. when I am taking the measurements which I need to be most careful about.


So

A1 : 1

A2 : 2

A3 : 3


B1 : 2

B2 : 3

B3 : 3


C1: =SUM(A1:A3)-SUM(B1:B3)


If i were then to do the same equation changing one variable:


A4 : 2

A5 : 2

A6 : 3


B4 : 2

B5 : 3

B6 : 3


C4: =SUM(A4:A6)-SUM(B4:B6)


I want excel to tell me that the original A1 value was changed and how this change in variable affected the overall solution?

I would imagine some form of if statement would be used, so


IF(A1=A4,,A4)or IF(A2=A5,,A5) or IF(A3=A6,,A4)....etc this will then use the variable that has been changed to calculate the % error from the original solution (C1)?

 

[NARAYANK991]

Hi ,


I think the percentage calculation is straightforward ; I'll just post the formula for finding out the cell which has changed.


Put the following formula , as an array formula , using CTRL SHIFT ENTER :


=ADDRESS(SMALL(IF((($A$1:$A$3)<>($A$5:$A$7)),ROW($A$1:$A$3)),1),1)


This should display $A$1 if the changed value is A1 , $A$2 if it is A2 and $A$3 if the changed value is A3.


Of course , if you make the changes to A5 , A6 or A7 , it will still display A1 , A2 or A3. Is this acceptable ?


I have assumed that only one change at a time will be made.


Narayan

 

[okeane2]

That is prefect, thank you so much!