A formula for expanding complex formulas???

**Solid Jexcel** · 12-11-2015, 09:38 AM

Hi, I'm trying to automate the task of creating new formulas based on a 'master' formula.

This is my master formula; (Starting at Lamp_2 as Lamp_1 is quite different. Once there are 2 or more lamps the pattern in the formula is very similar)

Please Login or Register  to view this content.

This is the next one;

Please Login or Register  to view this content.

And the next one;

Please Login or Register  to view this content.

Etc...

The difference between them is what I need to make automated as I need this pattern to carry on until I get up to Lamp_55!!!
What's happening at the minute is, because it was getting so complicated I had to name all the cells, which (correct me if I'm wrong) makes them an absolute reference? So when I try and drag the cell down, nothing is updating. I'm having to copy and paste the previous formula in the next cell, and go in and edit all the relevant lamp numbers, add the additional line of code at the end, and add an additional bracket on the end of the formula.

Any help would be appreciated.

Cheers

**jason.b75** · 12-11-2015, 11:14 AM

Because your formula is constructed entirely of named ranges it is impossible to see which references should be relative, and which (if any) should be absolute.

Could you post a sample workbook that includes the relevant data for the sample formula, and the formula without using named ranges?

**Solid Jexcel** · 12-14-2015, 08:56 AM

Ok, here's the formula without the named cell references;

For Lamp 2;

Please Login or Register  to view this content.

For Lamp 3;

Please Login or Register  to view this content.

For Lamp 4;

Please Login or Register  to view this content.

**Solid Jexcel** · 12-14-2015, 09:01 AM

I think it's basically a case of telling the formula to add in a new last line every time I drag and copy the cell down, but also updating the relative cell reference number with some sort of counter? The counter would have to count up in twos, 11, 13, 15 etc. It would also need to know to add in an additional bracket at the end to account for the additional line of formula.

**jason.b75** · 12-14-2015, 12:59 PM

There is no formula to 'extend' an existing formula, you have to work with a dynamic array to achieve what you want.

I think this should do the trick,

PHP Code:


=IF(OR(

     INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+13)<$BP$11,

     INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+13)>$BP$12-$BP$11,

     INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+14)<$BP$11,

     INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+14)>$BP$13-$BP$11,

     AND(INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+13)>$BP$13,

               INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+13)<$BY$7,

               INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+14)>$BV$7,

               INDEX(BJ:BJ,(ROWS(BJ$13:BJ13)-1)*2+14)<$BV$4),

     SUMPRODUCT(((INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+13)<$BJ$11:INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+11)+$BP$9)*

               (INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+13)>$BJ$11:INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+11)-$BP$9)*ISODD(ROW($BJ$11:INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+11))))*

               ((INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+14)<$BJ$12:INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+12)+$BP$9)*

               (INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+14)>$BJ$12:INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+12)+$BP$9)*ISEVEN(ROW($BJ$12:INDEX(BJ:BJ,(ROWS(BJ$13:BJ14)-1)*2+12)))))),

     $BP$14,"")

The SUMPRODUCT part is where it all happens, each time you copy the formula down 1 row, the range used in the formula increases by 2 rows, different sections of that formula then work with either the odd, or even numbered rows.

Everything above SUMPRODUCT is the common first park of your formula, rewritten so that the cells referred to in column BJ move down 2 rows each time you copy the formula down 1.

**Solid Jexcel** · 12-15-2015, 04:36 AM

Cool, really appreciate you having a crack at it.

I've copied your formula in and tried to copy it down the cells, but it's either me not using it correctly or maybe it needs a bit of tweaking?

If you look at the two attached jpegs, you can see that my labour intensive version looks at all the cells above it and returns the result it should, i.e. no ERROR warning. When I paste yours in it falls on certain cells and doesn't look at all the cells above.

Lamp Formula SJ Version.jpg
Lamp Formula J.B75 Version.jpg

Would it be easier if I attached the excel file I'm working in?

**jason.b75** · 12-15-2015, 12:02 PM

Hi, yes a copy of the file would make it easer to follow, it's easier to get accurate results with real work data than random test values.

Looks like you have the formula in merged cells, the biggest complex formula killer

I will need to re-evaluate my formula to allow for that. I might also be able to bury some of it in named ranges to make it less complex.

**jason.b75** · 12-15-2015, 03:37 PM

The first formula was based on increasing 2 rows each time you copy down 1 row, without taking merged cells into consideration, i.e. a 20 row table of data, being reported in a 10 row table of results. In the format you have, although you only have 10 cells with a formula, it's still 20 rows so the formula counts them incorrectly.

Because of this, the first formula that incorrectly returned "ERROR" was not actually looking at the data in the same row as the formula, but looking at data further down the sheet. It's not that it wasn't looking at the rows above, but rather that it was looking at them and some rows below as well

The formula in the merged cell adjacent to lamp2x and lamp2y was processing the data for lamps 1 and 2 (x and y), if the cells were not merged, the top one would have looked at lamp 1 (x and z) the bottom one at lamp 2 (x and y).

Moving down the column, the formula next to lamp3x and lamp3y was actually looking at the data for lamps 3 and 4. By the time you get to the first 'ERROR', following this pattern, you will notice that the formula will, theoretically be looking at lamps 11 and 12, which, as the cells are empty, don't fall within the tolerances.

If you created a new results table elsewhere on the sheet, with lamp1 to lamp10 listed down in single cells (1 row per lamp), then copy my original formula next to lamp 2 and down, then you should find the results are as expected.

Working with your existing layout and merged cells, this less complex version should give the expected results.

Enter into the merged cell BK12:BK13 and copy down as needed.

PHP Code:


=IF(OR(BJ13<$BP$11,BJ13>$BP$12-$BP$11,BJ14<$BP$11,BJ14>$BP$13-$BP$11,
AND(BJ13>$BP$13,BJ13<$BY$7,BJ14>$BV$7,BJ14<$BV$4),
SUMPRODUCT(((BJ13<$BJ$11:BJ11+$BP$9)*
(BJ13>$BJ$11:BJ11-$BP$9)*ISODD(ROW($BJ$11:BJ11)))*
((BJ14<$BJ$12:BJ12+$BP$9)*
(BJ14>$BJ$12:BJ12-$BP$9))*ISEVEN(ROW($BJ$12:BJ12)))),
$BP$14,
"")

As your merged cells occupy 2 rows, the formula ranges will increase by 2 anyway as you copy down, so there is no need for the extra functions used for that purpose in the first formula (INDEX(BJ:BJ,ROWS(BJ$13:BJ13)-1)*2+13), etc.

Effectively, this is the same as your original lamp2 formula, with sumproduct replacing the final AND(). The difference being that the sumproduct part expands to include multiple rows, so you don't need to keep adding extra bits.

The first part of your formula had a lot of bits repeated that didn't need to be, so I've just trimmed it down a little.

**Solid Jexcel** · 12-16-2015, 05:08 AM

You're an absolute genius! I thought that due to the niche nature of this request I'd never find the solution I wanted. Your formula works a treat so thanks very much!

I actually noticed a mistake on my part made during the switch back to non named cells, so here's the final formula below. Only one cell reference change in the AND function.

Please Login or Register  to view this content.

I'd actually read on the forum about the dangers of merged cells but because each ERROR message needed to apply to both the X and Y rows of each lamp I thought it was the best way, so I'm glad you found a way around it.

I've attached the file I've been working, just for reference. It's actually for a SolidWorks design table hence all the stuff before column AW. Columns BI:BJ are the user input areas.

Thanks again mate.