I tried using the solver for something, but I may have the wrong tool.
I have a spreadsheet I use to calculate the height of steel rebars in a wall. The bars have variable height over a specified length.
For now, much of it is done manually, eventually, I'll want to automate the whole thing, but I'm not there yet, I first need to learn VBA.
What I'm trying to do is find the correct value for a cell that will minimize the content of another cell.
What I do right now is input different value. I start at 0,01 and work from there increasing the value, inputing in between values. I wouldn't say it's the most time consuming part, but still I'd like to do it automatically.
So I have a table that lists my wall lengths with the corresponding rebar height.
So I start at 40' (12.192m) in column A, column B has the theoritical value of my max height (wall height minus 4"), column C rounds the value of B to the nearest 0,025 multiple, column D is the metric difference between C and B. I don't touch any of this, except to add/remove rows depending on the lenght of the wall and the spacing of the rebars, but I ain't touching this for now, so consider it fixed.
Then I have a smaller table where I input my values.
H6: rebar spacing
H8: starting height of the wall (in this case 8'/2.44m, changes everytime)
H10: final eight of the wall (here 6'/1.8m)
H12: Variation. This my variable cell, it gives me by how much each bar must vary from the preceding one in order to achieve my objective.
H14: Wall lenght
I18: Maximum difference in mm between theoritical and real height, should never be more than 1/2" (12.7mm)
I19: Minimum difference in mm between theoritical and real height, should be as close to possible as 0 and it usually is the same as I18, albeit negative.
H22: Difference in the rebar height between the final rebar and cell H10 minus 4". My objective is to minimize this cell.
So I tried the solver, first without constraint, to solve this simple problem.
Objective: H22, min.
Variable cells: H12
Never works. I put a zero in H22, didn't work. I put a 1, didn't work. I put 0.01, didn't work.
So what am I missing?
Solver can be an annoying bitch when you have rounding in formulas. That's because rounding creates discontinuous functions, which messes with the numerical algorithms Solver uses.
Quote from: DGuller on May 06, 2011, 05:08:20 PM
Solver can be an annoying bitch when you have rounding in formulas. That's because rounding creates discontinuous functions, which messes with the numerical algorithms Solver uses.
ah.
I'll try something different then.
EDIT: I copy/pasted the values just to see, but it doesn't work.
Did you figure this out?
Quote from: DGuller on May 09, 2011, 10:05:39 AM
Did you figure this out?
I haven't worked on it a lot. I just can't figure a way to make it work with the solver. I don't have a lot of iterations to do, maybe max 12-15 for the worst cases, so I'll just skip that step, do it manually and keep working on learning VBA to automate the reste of the spreadsheet.
Another Excel problem.
(Note: I am a complete idiot with Excel).
I need to manipulate old money (i.e., the equivalent of pounds, shilling, pence) with percentages. Now, I have made an easy formula which converts any amounts into pences, which in turn allows all sorts of manipulations, additions, substractions, etc.
What I would like is a formula which allows me to transform the final amount of pennies into pounds, shilling and pennies.
Reminder:
1 pound = 20 shilling
1 shilling = 12 pence
1 pound = 240 pennies
For example, I need an Excel formula to transform back 647 pennies into 2 pounds, 13 shilling, 11 pennies.
Are you using pence and pennies interchengeably?
Assuming you are, it's pretty simple.
The number of pounds is =trunc(647/240).
The number of shillings is =trunc((647 - pounds * 240)/12), where pounds is the first number.
The number of pennies is 647 - pounds * 240 - shillings * 12, where pounds and shillings are the first and second number.
Thanks! I was sure there was something that allowed me to ignore the decimals, but was not looking at the right place.
Well, searching for something a little more difficult...
I need to count the numbers in a table, according to a specific criteria.
The formula would look something like:
=SUMIF(A7:A164,"Acier vertical",E7:E164)
Now, the problem is, the text string is not a complete match, there's other text after this. But I need to count everything that includes these words.
Something like that:
=SUMIF(A7:A164,LEFT(A7:A164,14)="Acier vertical",E7:E164)
but obviously, it ain't working. I think Left doesn't work with a range of cells.
I need to count everything in the specified table when the first columns countains the first 14 characters wich are "Acier vertical".
So, if "Acier vertical" or "Acier Vertical 20M" or "Acier vertical variable", it needs to be counted.
How do I do that?
Can't you create a new column, which takes the first 14 characters of the first column, and use the new column in the SUMIF?
I would have if I had tought of this! duh!
thanks :)
ah, another one, easy answer, I'm sure :)
I have a table of variable length (rows only, columns are fixed).
First step: make it varry according to a specific value.
The last entry of the table must be close to 0, never go under. It will vary according to the parameters I input elsewhere in the workbook.
How would I do that?
Second step:
In one of the column, there will be a max and a min value. Say, Max=2.950m and Min=1.725m. Min and max values are automatically adjusted for the length of the table with the use of =indirect as part of my functions.
Between 2,950 and 1,725 entries will go down by 0,025.
I need to count each different value and report it in another table.
In the new table (let's say it starts at cell A1, in a new page wich will always be the next one in the spreadsheet) I want to automatically see:
1st column: "text string of my choice" (optinal, I can still type it once, and copy/paste)
2nd column: content of cell B2
3rd column: all of the unique values between max and min
4th column: number of times the value appears in the table TIMES cell B3.
So, how do I do that?
That's actually not that easy, at least not to my knowledge. Excel is not at its best when dealing with variable numbers of columns or rows.
The best thing I can think of is that you use an IF function to decrement, something like C30=IF((C29-0.025)>0,C29-0.025,""), and extend those formulas for the maximum possible number of rows. In theory, you're not going to have a variable number of rows, but you're going to have a variable number of rows showing up blank.
Then just make sure that formulas for every other column either generate or handle blank cells on their rows by their own IF statements. For example, D30=IF(C30<>"", whatever it would be, ""). It's not pretty, but it worked for me when I had to deal with variable row numbers.
I thought there was a way to create a variable length table in Excel 2010... hmm, I'll have to dig further before I go the multiple 'IF' way.
Thanks again :)
Got another one...
I want to create an e-mail hyperlink, from a value presented elsewhere in the worksheet.
So I use the function: "=HYPERLINK("mailto:"&"Courriel: "&J6)" where cel J6 contains my e-mail adress.
I actually want the adress to look like "Courriel:
[email protected]".
However, Excel always shows "mailto:" in front of my adress. How do I get it to disapear?
Quote from: viper37 on June 16, 2011, 10:19:02 AM
Got another one...
I want to create an e-mail hyperlink, from a value presented elsewhere in the worksheet.
So I use the function: "=HYPERLINK("mailto:"&"Courriel: "&J6)" where cel J6 contains my e-mail adress.
I actually want the adress to look like "Courriel: [email protected]".
However, Excel always shows "mailto:" in front of my adress. How do I get it to disapear?
Right Click the hyperlink. There's a dialog call "Text to Display".
There's no such thing when I right-click in Excel 2010. And the "hyperlink" button in the inster bar is greyed out.
Quote from: viper37 on June 16, 2011, 10:32:25 AM
There's no such thing when I right-click in Excel 2010. And the "hyperlink" button in the inster bar is greyed out.
Is there an Edit Hyperlink option?
My Excel 2010 is currently unavaible so can't check.
Ah, got it.
I had to create the link with the dialog box and change the text in this dialog box. Once created, I cannot in any way modify the text shows.
And I can't use the formula to create an hyperlink, and if I tried to use ="Courriel: "&J6 it wouldn't create a link.
But now, it works as intended. Excel is kinda tricky&capricious, but I'm taming the beast.
Thanks :)
No Problem.
You are trying to do crazy shits with it too.
I make nice borders :)
Quote from: Grey Fox on June 16, 2011, 10:47:08 AM
No Problem.
You are trying to do crazy shits with it too.
I make nice borders :)
another one... I'm displaying text&date. Got the format right. However, I'd like Excel to write "Juin" instead of "June" for the month... worst case scenario, I'd leave it in a short date format, but if I could get the long one...
Also, how do I modify part of a text that is inside a formula so it lookes like this?
="Soumission
modifiée le "&now" ?
Excel takes it's month from your Windows Regional Settings.
Quote from: Grey Fox on June 16, 2011, 01:34:41 PM
Excel takes it's month from your Windows Regional Settings.
I've changed it... but I believe I needed it to be set to "English Canada" for one particular piece of software... Anyway, I'll live with this.
Quote from: viper37 on June 16, 2011, 01:02:45 PM
Also, how do I modify part of a text that is inside a formula so it lookes like this?
="Soumission modifiée le "&now" ?
well, apparently, that can't be done in Excel, not even using VBA. :(
I'm trying to figure something that won't involve VBA...
So, I've got one of my worksheets that's used for the indexation of the projects.
All projects are listed there, with basic info (project #, project name, project owner, city, type, size, prices (3 columns)).
In Quattro Pro, I used to have a macro that created the index line when I created the project. It basically copied the info from my dialog box to the relevant cells.
Now, I wanted to try something different.
Let's say my macro will eventually create the project # and name in the first two columns, and from there, the rest will automatically adjust according to the project #.
Example:
Sheet: Index
Project #: F12705
So, Index:A4 contains "F12705". Index:B4 contains "project name" as an hyperlink to that sheet, also named "F12705". Eventually, I'll learn enough about VBA to do that automatically, as it was done in QP.
Now, if I wanted to have cell Index:C4 contain F12705:D1 where "F12705" is always the content of cell Index:A4 and D1 is always fixed, how would I do that?
I tried =A4&"D1", but that's not working. tried converting the value to text first, then merging it with "D1", but it ain't working either.
Any ideas or am I asking the impossible?
Quote from: viper37 on June 20, 2011, 11:35:52 AM
Example:
Sheet: Index
Project #: F12705
So, Index:A4 contains "F12705". Index:B4 contains "project name" as an hyperlink to that sheet, also named "F12705". Eventually, I'll learn enough about VBA to do that automatically, as it was done in QP.
Now, if I wanted to have cell Index:C4 contain F12705:D1 where "F12705" is always the content of cell Index:A4 and D1 is always fixed, how would I do that?
I tried =A4&"D1", but that's not working. tried converting the value to text first, then merging it with "D1", but it ain't working either.
Any ideas or am I asking the impossible?
You want to use INDIRECT("F12705!D1"). So, based on your sheet, INDIRECT(A4&"!D1") works.
that's the ! i keep forgetting...
many thanks :)
Quote from: viper37 on June 20, 2011, 01:46:14 PM
that's the ! i keep forgetting...
many thanks :)
The INDIRECT is also pretty important. =A4&"!D1" isn't going to work, but =INDIRECT(A4&"!D1") will.
Quote from: ulmont on June 20, 2011, 01:51:07 PM
Quote from: viper37 on June 20, 2011, 01:46:14 PM
that's the ! i keep forgetting...
many thanks :)
The INDIRECT is also pretty important. =A4&"!D1" isn't going to work, but =INDIRECT(A4&"!D1") will.
I had the INDIRECT part, I found the function after posting :)