I have a spreadsheet which I update regularly. Each update involves entering new values into one or more of the spreadsheet’s cells. One of the main results of each update is held in a single cell, let’s call it value X,. In a separate cell I would like to hold the maximum value of X over a number of updates, and in another cell its minimum. I have been unable to achieve this.
So please! - What am I missing ?
Welcome to the forum
Without seeing your worksheet to see how things are laid out and how X changes it is rather difficult to be precise.
Attach a workbook illustrating the before and after update look of the sheet and I can be a bit more specific
(click on Reply/GoAdvanced and look below
You need either a row or a column of cells holding "X" after each update and then adjacent TWO rows or columns for min + max calculated over required number of updates
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Many thanks for replying. I wasn’t sufficiently clear about my problem. There are no columns or rows involved. It boils down to just three cells. Let us say that cell 1 contains a value which is the result of calculations elsewhere, I’d like cell 2 to hold the hitherto minimum value that ever appeared in cell 1, and cell 3 to hold the hitherto maximum that ever appeared in cell 1. I thought it would be easy!
Each time the spreadsheet is updated there is a new value in cell 1. If the value in cell 1 is between the values in cells 2 and 3, they should both remain as they were before the update. If it's lower than the value in cell 2, cell 2 should be updated to show the new lowest value (the value in cell 1), with cell 3 unaffected. Similarly, if cell 1 has a value higher than that in cell 3, cell 3 should be made equal to cell 1, with cell 2 unaffected. ….. very much like the behaviour of a max-min thermometer! :o)
This probably isn't for everyone, and it is not my favorite overall method of doing this sort of thing (there is no record of the values in cell1, so you cannot go back and check or correct the calculation). If you are allowed to turn iteration on and use circular references, then something like cell3 = max(cell1,cell3) will keep track of the max values, and cell2 =min(cell1,cell2) will keep track of the min values.
You will probably need some further testing and development to get the initial values started. For example, if your values in cell1 will always be greater than 0, then you may need a test for 0 in cell2 to get cell2 off of its initial 0 value. =if(cell2=0,1e12,min(cell1,cell2)). You may need a similar test for cell3 if the values are always below 0. I also tend to like to incorporate a "reset" into my circular references/iteration calculations. =If(cell4=TRUE,resetvalue,circular reference formula).
Last edited by MrShorty; 02-25-2017 at 05:08 PM.
Originally Posted by shg
Right Click on your sheet name at the bottom of excel and select view code.
A new window will open.
Paste this code into the window and close it.
Enter Values into B2. C2 will store the minimum. D2 will store the maximum.
Please Login or Register to view this content.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Thank you very much MrShorty and mehmetcik. Wow! I’m not familiar with either of your approaches to my problem, so it’s going to take me some time to get my brain round your suggestions! In the meantime, a boost to your reputations :o)
Thank you again mahmeticik. I had absolutely no idea that an automatic macro such as the one you offered me could even exist ! But it simply works :o) A bonus too is that one can override the max and min values and the override is accepted without any fuss. Problem solved! :o)
And thank you too once again MrShorty. I have only ever considered iteration in connection with “Goal Seek”, and have regarded circular references as errors to be avoided. So I’m interested to realise that they can be “tamed” - I intend to devote time to getting to know them.
I don't know if these will help in your attempts to become familiar with the use of circular references, but here are a couple of further examples:And thank you too once again MrShorty. I have only ever considered iteration in connection with “Goal Seek”, and have regarded circular references as errors to be avoided. So I’m interested to realise that they can be “tamed” - I intend to devote time to getting to know them.
One I put together to illustrate the use of the Newton Raphson method for finding roots: http://www.excelforum.com/tips-and-t...ind-roots.html
One someone else put together dealing with "strange attractors". If you are familiar with factorials (and the fancy things that can be drawn from them) then this should be interesting: http://www.excelforum.com/tips-and-t...eferences.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks