Solved

Excel (function for: input - output - feedback)

Posted on 2013-12-09
24
468 Views
Last Modified: 2013-12-10
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?


Thank in advance,
EEH
Snapshot.jpg
0
Comment
Question by:ExpExchHelp
  • 13
  • 11
24 Comments
 
LVL 76

Expert Comment

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

Author Comment

by:ExpExchHelp
Comment Utility
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
 
LVL 76

Expert Comment

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

Author Comment

by:ExpExchHelp
Comment Utility
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

http://www.youtube.com/watch?v=JJ6orMfmorg

Hope this helps.

Thanks in advance,
EEH
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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

Open in new window

0
 

Author Comment

by:ExpExchHelp
Comment Utility
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?

Again, thanks in advance,
EEH
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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 Comment

by:ExpExchHelp
Comment Utility
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
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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 Comment

by:ExpExchHelp
Comment Utility
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
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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 Comment

by:ExpExchHelp
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
Comment Utility
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
 

Author Comment

by:ExpExchHelp
Comment Utility
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
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
No Green or Red
0
 

Author Comment

by:ExpExchHelp
Comment Utility
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 Comment

by:ExpExchHelp
Comment Utility
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 Closing Comment

by:ExpExchHelp
Comment Utility
Excellent framework!!!   Thank you!
0
 

Author Comment

by:ExpExchHelp
Comment Utility
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?

Your random function (+- 30%)
=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
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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 Comment

by:ExpExchHelp
Comment Utility
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
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
I'll try to have another look at this tomorrow
0
 

Author Comment

by:ExpExchHelp
Comment Utility
Thanks... 'much appreciated!  ;)

Cheers,
EEH
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now