# Excel (function for: input - output - feedback)

Hello Experts:

I'd like some assistance with modeling an input/output/feedback function.

An example (see attached JPG) was found in an older publication.   I'd like to replicate the changes in the I/O and feedback so that a given value (e.g., 6) is constantly held.

Again, please see attached JPG first.    Based on the quoted paragraph, the following data is summarized:

Summary of values:
================
Input value is 3
Output value is 6
Output reading changes to 8 (for unknown reason)
Output deviation = 2
Input value registering 4
Feedback circuit output deviation of 2
Input reduced to 2
Transfer function will double
New output will be 4 instead of 6
Feedback circuit needs its own transfer function which will take the output deviation of 2, reduce it to 1
Output will now return satisfactorily to 6 (because its input has been corrected to 3)

My questions:
1. First and foremost, can Excel be utilized to mimic this process flow (whether it's based on the initial output reading change from 6 to 8 or from 6 to any other number)?

2. "Icing to the cake"... if yes to #1, can those changes be animated and show the values of the I, O, and feedback at each of these timeslices?

If "no" is the answer to both questions, what are some tools that could accomplish mimicking this particular data flow?

EEH
Snapshot.jpg
###### Who is Participating?

RetiredCommented:
Here is a slightly re-organised sample.

The measured output level is the expected level plus or minus 30%. produced using the RAND function as you suggested. The previous feedback level is not involved in each calculation as it was in the first workbook. I've formatted everything to two decimal places.
Book2.xlsx
0

RetiredCommented:
The maths looks easy, but it isn't clear what the objective is. Can you explain that bit, please?
0

Author Commented:
GrahamSkan:

Trying to take this conceptual example in order to mimick Beer's transfer function scenario.

While the math appears to be easy (as you indicated), the importance is the time element.   That is, as time progresses (e.g., the output reading changed from 6 to 8).   That's at, e.g., t+5 (where t = time).

Then, at t+6, the equilibrium state (i.e., 6 units) should be reached again.    The output reading at t+7, however, could change again (this time from 6 to 5 units).

How can this be modeled in Excel?   I'd welcome any examples you could offer.

Thanks,
EEH
0

RetiredCommented:
So, is the output to be adjusted to a fixed value (6 in this example), regardless of the input?
0

Author Commented:
Yes, in this example, the output is a fixed value = 6.

As time progresses, however, there should be variable number of oscillations built in.   For these, I'd like to output their values (tabular or graphical or both) but then show that "equilibrium" was achieved again.

A YouTube video may facilitate the discussion.   Here Stafford Beer discusses the steam governor process (same principle).    There's a ~65 seconds time slice (35:30 to 36:35) in this video which addresses the topic

Hope this helps.

EEH
0

RetiredCommented:
I'm sorry, I live in a village, with only nominal broadband, so have trouble using Youtube.

Here is a VBA demo that inputs a number of randomised inputs and shows the calculation and application of a correction factor.
``````Sub Feedback()
Dim sngInputFactor As Single
Dim sngOutputValue As Single
Dim sngCorrectedOutputValue As Single
Dim sngDeviation As Single
Dim sngOutput As Single
Dim sngOutputTarget As Single
Dim sngFeedbackFactor As Single
Dim sngCombinedFactor As Single
Dim sngInputValue As Single
Dim i As Integer

sngInputValue = 3
sngOutputTarget = 6
sngFeedbackFactor = 1

sngInputFactor = sngOutputTarget * sngFeedbackFactor / sngInputValue

For i = 1 To 10
sngInputValue = 1 + Rnd * 10
sngOutputValue = sngInputFactor * sngFeedbackFactor * sngInputValue
sngCombinedFactor = sngOutputTarget / sngInputValue
sngFeedbackFactor = sngInputFactor / sngCombinedFactor
sngCorrectedOutputValue = sngInputValue * sngInputFactor / sngFeedbackFactor
Debug.Print sngInputValue, sngOutputValue, sngFeedbackFactor, sngCorrectedOutputValue
Next i
End Sub
``````
0

Author Commented:
GrahamSkan:

Thank you for the VBA... 'not sure how to actually implement this into the Excel.

That is, if the target cell is A1 (containing value of 6), how would this VBA be utilized?

Would you posting an XLS and include a few comments on execution?

EEH
0

RetiredCommented:
It is VBA only, to see if I have the right idea. The output goes to the Immediate window in the VBA IDE. I'll have a go at converting it so that it populates some cells in a spreadsheet.
0

Author Commented:
GrahamSkan:

Conceptually, your code makes sense to me (but I'm not a professional programmer).

Here's the only thing to keep in mind.   The fixed value of "6" is based on Beer's example.

For instance, today's goal might be to output 6 items.   For tomorrow or next week, however, the fixed value could change from 6 to 18.   So, as long there's an input cell (e.g., A1) which allows to enter the desired output value (and thus automatically forcing the adjustment of oscillations values), it should be ok.

Makes sense?

EEH
0

RetiredCommented:
I have attached a sample workbook. It does not use VBA

The yellow cells expect manual input. The others are calculated
Book1.xlsx
0

Author Commented:
GrahamSkan:

Most excellent!!!   May I ask two follow-up questions?

1. On the XLS, what column is the equivalent to the "8" (original posting where I indicated "output reading changes to 8 (for unknown reasons))?   Which one is the "deviation" column (see original posting)?

2. Per the attached (new) JPG, which columns/fields equal which in the graph?

3. Why did you suggest that column D (Input Level) should be modified?   Is this a random number?   Or shouldn't it be derived from another value?

4. If I wanted to graph the oscillations, do you concur that column E (Output level) should be used?

Again, thousand thanks for your help!!

EEH
Snapshot.jpg
0

RetiredCommented:
My understanding of the requirements might be limited, but in the sheet the Output Level is derived from the Input Level, so any variation in the Input Level is countered by changing the feedback factor.

1. If you put a 4 in cell D3, the Output Level in E3 will show 8. A new feedback factor is then calculated and applied so that the New Output Level is brought back to 6.

2. You have attached a flow chart, not a graph.

3. The Input Levels in Column D are manual entries.

5. I don't know enough about the objective to be definite, but inputs from any columns could be used in a graph. However I would exclude columns A,B and G because they are always the same. Column C is the same as Column F, but shows the value from the previous row.
0

Author Commented:
GrahamSkan:

Again, I truly thank you for your help.... 'very much appreciated.

Also, thanks for the additional information.    I've had a closer look at the formulas.   There may be a slight problem when comparing the flow with the requirements (per original posting).

Here's the key... any adjustment to the "Input Value" must be automatically derived based on the deviation on the output reading.

When I manually changed the value in column D (Input Level) all values are the same.   Beer's function, however, suggests that the input value is automatically adjusted based on the deviation of the output reading (e.g., the change from 6 to 8).

Here's what I'd like to figure out... add another column that holds the deviation reading.   Maybe this should be a random number (RAND function) that generates an upward/downward reading of up to 30% from the last output level.     Then, based on that deviation, the new input value is automatically derived.

How would you solve that in the current framework?

EEH
0

Author Commented:
GrahamSkan:

Thank you for providing the modified XLS... I appreciate it.

The solution is closer but I think there's still a conceptual gap.    Here's what's missing...

Based on Beer's transfer function, the input level (currently fixed at "3") will vary.

The deviation output seems to be working fine.   So, that's good.   However, based on the deviation, the new input should be either greater or lower than 3.

At t=0, the input equals 3.
Then, output equals 6.
Then, deviation (+ - 30% is recognized at output level).
Based on the deviation, a "signal" is sent to the input which now has to modify its input value... that modified value is based on the deviation factor.

So, basically, there will be oscillation in the inputs, the deviations, but the actual output of (6) should be consistent regardless of the oscillations.

In other words, the output of "6" should be set outside all the formulas.  The same holds up for system factor.   From then on, output will always be 6 but all other (or most) factors  will be adjusted.

Please see the attached XLS.   Columns marked in red should be removed (instead, values in B1 and B2) should be used.   Columns marked in green seem to be ok.   Column marked in orange is TBD... not exactly sure.

Makes sense?

Again, I truly thank you for your assistance.

EEH
Book2.xls
0

RetiredCommented:
No Green or Red
0

Author Commented:
Sorry... my error (too many files).

Here it is.    Thanks in advance for having another look.

I hope the additional info makes sense.

EEH
Book2-modified.xls
0

Author Commented:
GrahamSkan:

Based on your recommendation, I think I got it figure out.

Please see attached XLS.   Each time you click the "Save" button, it's recomputing the input values.

Based on what I described in the earlier post, do you see any issues w/ this modified approach?

Thanks,
EEH
Book3.xls
0

Author Commented:
Excellent framework!!!   Thank you!
0

Author Commented:
Oh... final follow-up question.

I may need to integrate an "expected" deviation level.   For instance, cell B3 may need to contain 30% (or any other value).

When looking your your random function (for the +- 30%), I'm not entirely certain if I can make sense of it.   What's the "*60" used for?

=B6+ (30 - RAND()*60) * B6/100

Versus other random function (not modified to reference proper cells):
=RANDBETWEEN(B6-\$B\$4*A3,B6+\$B\$4*B6)

... w/ the above, it would always use the value in B4.

Again, I'll be good to go once I have a better understanding about your random function.

Thanks,
EEH
0

RetiredCommented:
Not sure that wasn't in essence, what was happening in the first example, where the input varied (though the variation was derived manually as opposed to an automated randomness).

However, I have removed the two columns. They were there to make the setting-up easier.

The feedback factor is now set again to look at the input value and to derive a multiplier to be used  to force the output to the target value.

I have avoided a bit of work on my part by reducing the  number of input items. We can no longer drag down to calculate the results for later rows as one of the cells in the formulae is fixed and not to be incremented, while the other is row-dependent and must be so incremented.
Book3.xlsx
0

RetiredCommented:
Sorry. I left my browser open at this question for some hours while I was called away, so my last comment night now be irrelevant.
0

Author Commented:
GrahamSkan:

Thanks... no worries... I appreciate your help on this.

Did you see my last comment on the random number function.    I'm planning on adding a fixed input field (w/ changing values) for that.

Not sure if I completely understand where the, e.g., "60" (in =B6+ (30 - RAND()*60) * B6/100) comes from.   Would you mind providing more details how this function works (in contrast to the random # function I provided in previous comment)?

Thanks,
EEH
0

RetiredCommented:
I'll try to have another look at this tomorrow
0

Author Commented:
Thanks... 'much appreciated!  ;)

Cheers,
EEH
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.