• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

cycle though formulas to show best result?

Harry0

Harry0

Member
Can excel or how can excel look at various formulas to find the best result.
instead of laying out a big chart of 100000 results and scan though them, anyway to make a row of a few hundred or less to scan thousands of formulas for the best result without showing every possibility on screen but to look for it?

or in other words for example
if row A1 selects which % to look for from A3 to B100 , and A2 shows what overall amount of likelihood it has to show it.
So instead of putting every possible % in A1 manually any way to find what is the best automatically to get the best # for A2?

If a2 is the best number then dont calculate another number for a1
Since what if
a1 = 1% and a2 = 50% which is not as good as
A1 =5.5% and a2 = 90% which is a better result
thanks
 
Harry
There are a few ways to tackle this style problem

Can you post a sample file as an example of what you want?
 
The file was ore confusing which took me time to try and simplify it.
Their are more but this is a basic one.
The blue cells can be edits to mix and match to find best %
But as you see doing it manually takes a long time and spreading it out also takes a long time and a lot of processing.
Thanks
 

Attachments

  • find.xlsx
    13.6 KB · Views: 5
Thanx Harry
Can you please explain what A1:A4 do and what range of values for each cell are allowable?
 
A1 is the minimum % that should be shown from column C which results are displayed on E. So if C says .5% and a1 is .07% then it will excluse that row. Which C references column A

A2 similar to A1 but looks at column B in what should be displayed which seems if their is a big enough gap between the current row and the previous.

A3 is used by D to separate the E if it has 1 2 3 or 4. 1 meaning if both statements are true, 2 means if true and false, false and true, 4 false and false.

A4 sees the final results if true by looking if column A reaches A4 criteria minimum gain %.

D1 stated how many in column D fulfill the results since 1 means yes (totaled in D3) and 0 means no (totaled in D4)
D2 sets the minimum results total is should display since only having 3 results does not give an accurate about tested. made it less for the example but increased it when i have 1000 rows.

I get confused as well when they become long and wonder how many can remember things, since it can be mental strain. I had to evaluate the code again to try and remember since their is too much info to remember and i even reduced it to try and simplify it to present it to you.
The formulas are sloppy since I keep modifying them in D and E so plenty of temporary unused info which I am changing criteria often to see probability.
I did not take info account negative numbers since that is even more formulas which I can not handle since I am forgetting the current ones.
Also other criteria is not presented which would change column A and B, which I am mainly using google sheets which allows A B to be downloaded.

Thanks
 
I am traveling for next few days
Can’t look at it til mid next week
Sorry
 
You must log in or register to reply here.
Back
Top