Excel hell

[viper37]

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?

[DGuller]

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.

[viper37]

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.

[DGuller]

Did you figure this out?

[viper37]

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.

[Oexmelin]

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.

[DGuller]

Are you using pence and pennies interchengeably?

[DGuller]

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.

[Oexmelin]

Thanks! I was sure there was something that allowed me to ignore the decimals, but was not looking at the right place.

[viper37]

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?

[DGuller]

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?

[viper37]

I would have if I had tought of this!  duh!
thanks

[viper37]

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?

[DGuller]

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.

[viper37]

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