Calculate based on logic of surrounding cells

[slarti]

Dear All,

You've helped greatly before, so I hope you can shed some light on this puzzle. I have created a simple structured bill of materials that determines whether a part on a vehicle is an assembly (eg the whole engine), or a child part of that assembly (eg a piston) via very simple logic. I would now like to use this logic to perform calculations to total up the cost of the engine and then use that to calculate the cost of the whole vehicle by adding up all the assemblies in it (but not including the cost of the child parts as they would be counted twice).

I've attached a simple worksheet that hopefully better illustrates what I'd like to do. This BOM has manual calculations in the cost column in place of anything more clever, but these show what the end result should be. I've also tried to describe what the calculation should be, so I hope this column makes sense.

Now the honest part, I have not got a clue for how to go about this calculation. :dunno I assume VBA would be best, but it may be possible with some hidden columns and calculations. If anyone can either solve the problem entirely, or at least give me some pointers to get started, I'd be exceedingly grateful.

If the first bit can be solved, I'd like to add a further complication: whereby sometimes a cost will need to be entered for the assembly, but it is still necessary to see its child parts and these will have zero cost!

Thanks in advance

Slarti

[Bob Phillips]

How about a formula?

In M3

=IF(G3="Assembly",SUM(D3:INDEX($D$1:$D$33,IF(COUNTIF(G4:$G$33,"Assembly")=0,MAX(ROW(G4:$G$33)),MIN(IF(G4:$G$33="Assembly",ROW(G4:$G$33)))-1))),"")

and copy down.

It is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range.

[slarti]

Indeed a most cunning formula!

Sadly, it doesn't quite work. I've added it to the attached spreadsheet, but it doesn't sum in the way I need. I've added what the result should be in the column next to the formula result, and for instance you'll see that line 14 (Rear wheel) should add in line 20 (hub) as this is a sub assembly of the wheel. Then gradually as you work up the assemblies, you should see what the whole bike costs (which is zero from this formula). Like wise line 8 should only sum lines 9&10, as lines 11,12&13 are an equal assembly level to line 8, not a sub assembly of it.

I hope this makes sense, sorry for any confusion!

Slarti

[Bob Phillips]

That's funny, I have just plugged the formula into the workbook and I get exactly the same results as you say you want.

[david000]

Columns E and F aren't doing anything for me - you could delete them and use:

=IF(A2<A3,"Assembly","Child part") just the same.

Also, you should think about using Autofilter with the SUBTOTAL function to sum the "levels" of assembly or a pivot table may work.

And xld ---

Can you do me a favor and link to ANY Excel MVP'S or Gurus that explain the use of the "Colon" or "Rang operator" in formula constructs. In reference to "D3 :INDEX"

I collect them when I find them, but they are often confusing for even battle scared Excel users.

[slarti]

David,

You've raised something I was going to mention, I'm fairly new to complex formulae and VBA, hence am constantly finding better ways to do things via this forum.

XLD,
One thing that would help greatly would be a descriptive break down of your formula, as although I understand each function that you've used in a stand alone sense, I'm struggling to see how they interact.

Folks,
I've not had time to fully investigate either of your suggestions as I'm dashing out of the office now, but as I've said above, it would be great to better understand the intricacies of Excel, so if you can help clarify your points that would be hugely helpful.

Thanks - Slarti

[Bob Phillips]

And xld ---

Can you do me a favor and link to ANY Excel MVP'S or Gurus that explain the use of the "Colon" or "Rang operator" in formula constructs. In reference to "D3 :INDEX"

I have never ever seen any explanation of that, it is so fundamental I guess everyone thinks that you either understand it or you don't use Excel.

Of course, that is fine until someone doesn't something left-field with it, as I am doing a bit here, so perhaps I can approach it from that angle.

Basically, all multi-cell ranges are defined as first_cell:last_cell, so the colon is telling Excel that we have a multi-cell range here (do not forget, a single cell is still a range, so we have to let Excel know). This much I am sure that you know already. However, and this is what may be not so obvious to you, the reference to first_cll or last_cell does not have to be a cell reference, it can be a function that returns a cell. That is what my INDEX function is doing.

Any help?

[Bob Phillips]

XLD,
One thing that would help greatly would be a descriptive break down of your formula, as although I understand each function that you've used in a stand alone sense, I'm struggling to see how they interact.

The basic logic is that the formula will look-ahead from the row that it is on until it meets a row with 'Assembly' in column G. It then sums column D from the row that it is on until the row before.

This part

IF(G4:$G$33="Assembly",ROW(G4:$G$33))
checks every row from the current row until the last row and returns an array of the row numbers where the condition is met - note that the G$ is relative, so it shifts every row, and thus does not include previous.

Once we have that array of roow numbers, we want the next occurrence, so we use

MIN(IF(G4:$G$33="Assembly",ROW(G4:$G$33)))
and subtract 1 to get the row previous.

We then want to get a reference to this cell so we COULD use

INDEX($D$1:$D$33,MIN(IF(G4:$G$33="Assembly",ROW(G4:$G$33)))-1)
which uses that found row number to pass to the INDEX function as the row index for the WHOLE range D1:D33, but as the last occurrence of 'Assembly' will not be able to find another one in the look-ahead routine, we need to cater for it. We do this by another look-ahead and counting how many instances of 'Assembly' we find, if it is none, we know we are in the last block, so our final row number is the row of the last data items. This is catered for with

IF(COUNTIF(G4:$G$33,"Assembly")=0 ,MAX(ROW(G4:$G$33))
which added to our previous look-ahead routine and combined with INDEX we have

INDEX($D$1:$D$33,IF(COUNTIF(G4:$G$33,"Assembly")=0 ,MAX(ROW(G4:$G$33)),MIN(IF(G4:$G$33="Assembly",ROW(G4:$G$33)))-1))

SUM is then used from column D of the current row to that cell reference to get the group sum

SUM(D3:INDEX($D$1:$D$33,IF(COUNTIF(G4:$G$33,"Assembly")=0 ,MAX(ROW(G4:$G$33)),MIN(IF(G4:$G$33="Assembly",ROW(G4:$G$33)))-1)))

And finally, we test if the current row contans 'Assembly, in which case we calculate the sum, else we output an empty string.


=IF(G3="Assembly",SUM(D3:INDEX($D$1:$D$33,IF(COUNTIF(G4:$G$33,"Assembly")=0 ,MAX(ROW(G4:$G$33)),MIN(IF(G4:$G$33="Assembly",ROW(G4:$G$33)))-1))),"")

[slarti]

Thanks XLD,

That explanation was exactly what I needed, and as I said before, it is a most cunning calculation. However, sadly now that it has been fully explained, I do not believe that it can give the correct result.

The problem is most obvious at the top of the BOM, where line 3 (the whole bicycle) should do the following:

Check each row for an assembly number that is one higher than itself (it is 0, so it should look for 1s), and then sum the cost for each instance of assy level 1, until it finds the next assy level 0, thus reporting a cost of ?143.80.

There are two reasons it has to be done this way:

1 - There can be sub assemblies mid way through the structure of the next assy level up, see lines 4 to 13, where assy level 2 occurs as the main frame, the front fork assembly, the bearing and the nut.

2 - The total cost of the bicycle is only reached by summing all of the level 1 assy's, but the calculation stops at the next assembly (the frame assembly).

Thank you for your dedication to this problem, and particularly thanks for the explanation, but I'm not quite sure how to structure a calculation to give the desired result, although I am a lot closer!

I'll ponder some more - Slarti

[Bob Phillips]

Do you want to post an example with what the REAL numbers should be in column M. Your example showed 1, my calculation gave 1. I am struggling to see where a 2 cuts off, 3 cuts-in etc.

[slarti]

Hi XLD,

I thought the formula should be
=IF(G3="Assembly",SUM(D3:INDEX($D$1:$D$33,IF(COUNTIF(G4:$G$33,"Assembly")=0,MAX(ROW(G4:$G$33)),MIN(IF(A4:$A$33=A4,ROW(G4:$G$33)))-1))),"")

Which I hope now looks for the next equal value in column A.

I've attached the spreadsheet with this formula in column M, and with the manual calcs showing as the bold figures in column D.

But this doesn't work (although it sort of appears too, but it's just bring in the subtotals from the manual calcs), and I'm not quite sure why. Also I've thought of another problem, assy level 0 needs to add up the costs for all assy level 1's, but these aren't there until this formula has run.

Can I ask for your help one more time please?

Thanks - Slarti

[slarti]

OK, well I'm getting there,

The formula is now
=IF(G3="Assembly",SUM(D3:INDEX($D$1:$D$33,IF(COUNTIF(G4:$G$33,"Assembly")=0,MAX(ROW(G4:$G$33)),MIN(IF(A4:$A$33<=A3,ROW(G4:$G$33)))-1))),"")

So the <=A3 is now looking at the correct cell (not A4 as previous post), and the <= is there to cope with assy number that jump by more than 1 level.

And this works perfectly for all but the first row, where it returns #VALUE!, any ideas (see attached spreadsheet)

Slarti

P.S, ignore my last post as this one has a better spreadsheet, and is a stage further on.

[Bob Phillips]

=IF(G3="Assembly",SUM(D3:INDEX($D$1:$D$33,IF(OR(COUNTIF(A4:$A$33,A3)=0,COUNTIF(G4:$G$33,"Assembly")=0),MAX(ROW(G4:$G$33)),MIN(IF(A4:$A$33<=A3,ROW(G4:$G$33)))-1))),"")

[slarti]

Thanks XLD,

You've done the double whammy, solved my problem and taught me a thing or two about Excel! This seems to be spot on, so I've now sent it out for trials with a few users to see if they manange to corrupt it in any way.

I have to say that this forum (and thus its members) is a simply the best Excel and VBA resource. It has never let me down, and always solved the problem, either through searches of other threads, or posting questions like this.

Thanks all,

Slarti

[slarti]

Hi, it's been a very long time since I raised this post and everything was going great with the formula that was so kindly created for me. However, I am now having a problem that I cannot fathom, whereby if I insert any row or rows above my headings, the formula returns #REF! and I cannot fathom why this is happening.

The attached file has two tabs one named "Original", where everything works as it should, the other "Blank Row At Top" has exactly that, with no other changes and #REF! is returned in two locations. Can anyone explain why this is happening, as even though I know the formula logic, I cannot work out why it's happening.

Thanks in advance,

Slarti29380

[Bob Phillips]

Blimey, fourteen years you have been using that? That really makes my effort worthwhile, it's as long as Munira Mirza was fawning over our PM :confused2

Anyway, the problem is that by adding that blank row, the maximum row number is 33. But you only have 32 rows that you are INDEX'ing into, so you could be looking beyond the data. To fix, simply need to subtract 1 from the MAX calculation

=IF(H16="Assembly",SUM(F16:INDEX($F$2:$F$33,IF(OR(COUNTIF(A17:$A$33,A16)=0,COUNTIF(H17:$H$33,"Assembly")=0),MAX(ROW(H17:$H$33))-1,MIN(IF(A17:$A$33<=A16,ROW(H17:$H$33)))-1))),"")

[SamT]

One way to keep some formulas from breaking is to 'Cut' an empty row from somewhere and 'insert' that row where you need a new blank row.

[slarti]

Thank you Bob,

That's partially solved that little quandary and thank you again for your original help. Yes I have been using that solution and many more from this site for fourteen years and more, it really is a testament to how useful this site and its contributors are.

Playing with it a little more, by adding for example two rows in, once again causes issues, even if I subtract 2 from the MAX calculation. What would be a better way to make this more robust, could a named range help in this instance, or is there a better way to define the INDEXing range?

[Bob Phillips]

A named range might help, but I don't think by much as the meat of the formula is determining what part of the range is used for each row. Maybe a named formula would be better.

First though, let's give the more robust formula that should cater for as many or as few blanks rows you add in

=IF(H3="Assembly",SUM($F3:INDEX($F$2:$F$33,IF(OR(COUNTIF($A4:$A$33,$A3)=0, COUNTIF($H4:$H$33,"Assembly")=0),MAX(ROW($H4:$H$33)-ROW($A$2)+1),MIN(IF($A4:$A$33<=A3,ROW($H4:$H$33)-ROW($A$2)+1))))),"")

To create a named formula, select cell H3 (assuming just the one blank row at top in this instance) and then go to the Name Manager and add a new name, let's call it last.row.this.assembly with a formula of

=IF(OR(COUNTIF($A4:$A$33,$A3)=0, COUNTIF($H4:$H$33,"Assembly")=0),MAX(ROW($H4:$H$33)-ROW($A$2)+1),MIN(IF($A4:$A$33<=A3,ROW($H4:$H$33)-ROW($A$2)+1)))

The formula in H3 down then becomes

=IF(H3="Assembly",SUM($F3:INDEX($F$2:$F$33,last.row.this.assembly)),"")

still an array formula. This does make the solution a tad more obscure, but more readable IMO.

[Bob Phillips]

Looking at this some more, I am confused as to why the assembly in row 9 (Original) calculates to £124. Shouldn't it just sum rows 9-12, £17.60?

[RPFeynman06]

The issue seems to be with the minimum portion of the formula. I know you want to have an ongoing subtotal and this seems way too complicated to achieve that robustly. I will look at it and propose solution.:yes

[slarti]

Looking at this some more, I am confused as to why the assembly in row 9 (Original) calculates to £124. Shouldn't it just sum rows 9-12, £17.60?

Yes you're right, it should be £17.60 and I'm a little confused too. Using your latest formula provides the correct total values in all the instances I've tested it against (see attached), so thank you for that, and why didn't I think of that tweak!

I'll keep an eye on the formula in use now and see if any oddities occur.

I haven't explored named formulae before, thanks for that tip. I'll use it in the future, but will keep this calculation a little more readable.

Thanks again,

Slarti

[slarti]

Correction, it does seem to throw up some odd total values now that I've played with it some more.

[Bob Phillips]

This is what I think is the correct formula for the data. I even tried it with a new level 0 slotted in, and it seemed to hold up.

I think we can simplify the formula, and make it clearer with a few named formulae.

- Select cell G3, again assuming one blank row at the top
- add a name, max.row.full.assembly, formula of =MAX(ROW($H4:$H$33)-ROW($A$2)+1)
- change the formula for the name last.row.this.assembly to =MIN(IF($A4:$A$33<=$A3,ROW($H4:$H$33)-ROW($A$2)+1))
- add a name assembly.index with a formula of =IF(COUNTIF($A3:$A$33,$A3)=0,max.row.full.assembly,last.row.this.assembly)
- the formula in G3 and down then becomes =IF(H3="Assembly",SUM($F3:INDEX($F$2:$F$33,assembly.index)),""), which doesn't have to be array-entered

If you don't want all of the named formulae, then just use in G3 down
=IF(H3="Assembly",SUM($F3:INDEX($F$2:$F$33,IF(COUNTIF($A3:$A$33,$A3)=0,MAX(ROW($H4:$H$33)-ROW($A$2)+1),MIN(IF($A4:$A$33<=$A3,ROW($H4:$H$33)-ROW($A$2)+1))))),""), which does have to be array-entered.


If this is now robust, please supply some more examples with expected results.

[slarti]

I've just tried the non-named formula and sadly it doesn't calculate correctly. I've attached a new file, with the instances that are calculating correctly highlighted in green and those that are incorrect in yellow, with comments. For some reason the £20 in cell F17 is being included in all of the calculations above. I'm a bit brain dead after a long day, so can't see the logic of why this isn't working. Hopefully someone who's more alert can spot what's wrong.

Thanks in advance.

[Bob Phillips]

I am totally at a loss as to why that data should be so wrong, so I started from first principles again. Maybe my formula skills are better than 14 years ago, or maybe I am missing something that I understood then, but anyway, U came up with this which is a lot simpler

=IF($H4="Assembly",SUM($F4:INDEX($F$4:$F$33,MIN(IF($A5:$A$34<=$A4,ROW($A5:$A$34)-ROW($A$3)-1)))),"")

I only tried it on the new data, so give a thorough work-out.

[slarti]

Thank you Bob,

I have good news, I've tested the new formula on different data sets over the last day and it seems totally infallible, with correct results every time. I can therefore conclude your formula skills are better than 14 years ago.

Thank you so much for all your help with this.

Mark

[Aussiebear]

Thank you Bob,
I can therefore conclude your formula skills are better than 14 years ago.


Bob's like a good wine, gets better as it ages.... By the time he's 150, he will be drinkable :devil2:.

[Bob Phillips]

I have good news, I've tested the new formula on different data sets over the last day and it seems totally infallible, with correct results every time.

...

Mark

Good to hear, and good that it is simpler. And very good to have a name to talk to Mark, hope it goes another 14 years.


Bob's like a good wine, gets better as it ages.... By the time he's 150, he will be drinkable :devil2:.

Certainly barrel aged, but if I survive that long, Lord forbid, I will be more likely pickled than drinkable.

[slarti]

Thanks again Bob, go easy on that aging! It's fair to say I owe you one for all this help, so stay in touch.

Mark

[Bob Phillips]

Dare I ask who benefits from your Excel interests Mark, the company that is, and where in England you reside?

[slarti]

I'd best not say, but I can say we're an engineering consultancy working in the majority of the transport sector (road vehicles, aircraft, marine & defence). I and the company are based in the Midlands. Sorry to be cagey.

[slarti]

I'd best not say, but I can say we're an engineering consultancy working in the majority of the transport sector (road vehicles, aircraft, marine &amp; defence). I and the company are based in the Midlands. Sorry to be cagey. Feel free to PM me if you need more.