Writing an expression - BAFFLED

Hello,

New to the forum and I was just wondering if anyone can help me write a formula as I am baffled by it. I am trying to generate numbers so that I can utilise these later to generate AutoCAD script, essentially this will help in providing co-ordinates for any given scenario.

I am trying to factor in different scenarios, although they may not always be used and I am trying to define this so excel can identify exactly what to use and where. Shown below is the problem I am stuck with

First Spacing - 250
Intermediate Spacing 1 - 310
No. of Intermediate 1 spacings - 7
Intermediate Spacing 2 - 200
No. of intermediate 2 spacings - 4
Intermediate Spacing 3 - 350
No. of Intermediate Spacing 3 - 3

I am trying to generate a column that will list spacing 1 to n and then in the next column generate the height (eg... 1 = 250, 2 = 560, 3 = 870 etc). I can write an expression if I am only using 1 spacing, but adding the second and third has confused me as I am unsure how to define and set the limits so that once it has gone past the first number of spacings it then looks at the second and adds them to the first to continue the running tota, then once the second has been utilised fully it will then utilise the third etc.

I have tried looking at IF commands along with AND commands but I am unsure how to express it in a matter that will work for any given situation. I have tried using (x-(x-1) where 1 is the spacing quantity etc to define the spacing number and x-1>=0 as the limit (so when x-1<0 it looks to the new set of numbers), however the problem with this is defining 1 (or 2, or 3) as something more than numerical values, I need to relate them back so excel knows how to determine them from the info provided (as previously shown)

Can anyone help? Please

Thanks in advance


Colin

It would help if you attached a sample Excel workbook, then we can see which cells you are using and tailor a solution to your data layout.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Hope this helps.

Pete

Pete,

Thanks very much for replying, I have just followed the steps provided and hopefully you will see the file attached to this.

Cheers

Colin

Hi Colin,

Your file uploaded fine. I'm going out soon, but I'll take a more detailed look at this in the afternoon.

Pete

Thanks very much, it will be greatly appreciated.

I've come up with a formula-based solution in the attached file. I've used a number of helper columns (coloured), all of which can be hidden once you are satisfied that the solution works okay if you want the file to look as it did when you posted it. I've tried to make it generic, so that it can cope with variations in the data.

The first set of helpers are in columns K, L, M and N, and are shown with a pale blue background - these can all be copied down to the bottom of your list, and as you can see I've copied to one row further. I put 1 in cell K3 and these formulae in the cells stated:

K4: =IF(LEFT(H4,5)="Final",1,IF(COUNTIF(H4,"*Quantity*"),I4,0))

L3: =IF(K3>0,MAX(L$2:L2)+1,"-")

M3: =IF(ROWS($1:1)>MAX(L:L),"",INDEX(K:K,MATCH(ROWS($1:1),L:L,0)))

N3: =IF(ISNUMBER(M3),M3+N2,"")

and I have also put zero in cell N2 (important). The formulae in K identify the quantity of each item in your list - I've assumed that the first and Final item only appear once. Column L identifies each quantity by means of a unique sequential number. Column M bunches those quantities up, and column N gives a cumulative sum of quantities.

The next two columns, P and Q (coloured pale green), expand the list in column N, using these formulae:

P3: =IF(ROWS($1:1)>MAX(N:N),"",MATCH(ROWS($1:1)-1,N:N)-1)

Q3: =IFERROR(INDEX(I:I,MATCH(P3,L:L,0)-IF(INDEX(I:I,MATCH(P3,L:L,0))<=10,1,0)),"")

Column P shows the repetitions of the appropriate Sequence number (L), for the number of times given in the Quantity column (K), and column Q gives the dimensions of each item as appropriate. (Actually, there is a bit of a fudge here - I've assumed that your dimensions will always be larger than 10, and that the number of repetitions will be 10 or less, in order to cope with alternating dimensions and quantities in your data in column H). From these, we can define your final table (shown with a grey background), using these formulae:

S3: =IF(P3="","",ROWS($1:1))

T3: =IF(S3="","",SUM(Q$3:Q3))

It is important that you capture all the data, so the formulae in the green and grey cells need to copied down sufficiently far to accommodate all the expanded data. I've copied these down to row 25 in the example file, but you should use the final number in column N as a guide to how many rows you really need.

Finally, to test out the solution on other data values, I've manually changed the data in I6:I7 (gold) and I8:I9 (orange) to see if that data gets included okay (It might be totally wrong assumptions on my part - obviously, the check in cell J4 shows FALSE, as I've not adjusted the initial part of the data). If you change these back to zeros you will see the table shrink automatically.

Hope this helps.

Pete

Pete,

Sorry about the delay in getting back, I have been too busy with work to get round to looking.

I have had a look at what you did this morning and it is ideal, its exactly the sort of thing that I was looking to do.

Thanks very much for your help with this, it really is appreciated

Colin

Hi Colin,

glad it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete