Calculating Values depending on Variable Suppliers

[Ricci2810]

Hi all,

Wondering if there is an easier way to calculate cost when changing variables for supplier and time of day, where another supplier could be added without having to greatly alter the entire sum.

Note the real world application I have now includes 2 suppliers and ten products, so the example isn't really feasible if a 3rd supplier was to be added.

[Beamernsw]

Hi Ricci, I've inserted a helper column as column A which can be hidden. Then used VLOOKUP to find the correct pricing row and the correct product column multiplied by the qty. This is repeated for each product so makes the formula quite long.
More suppliers can be added very easily. If you get more then 6 then the array size for the vlookup will need to be increased in the formula.
Adding more products would mean duplicating the VLOOKUP for that new column.

I'm fairly sure some kind of array formula could do this in 1 step but I'm just not very good with them yet

[Beamernsw]

Hey again, I've been playing with array formula (just for my own experience) and almost got one working really well.
But, alas, I still failed. But I tried another idea.
This one uses more helper rows at the top (which should be hidden ofcourse) but basically splits my really long formula into individual cells. The benefit of this is that it will be much easier to add more products later.
Either way, the price table (array range) should be saved as a named range. That way if you ever add more products or suppliers to the price table, all you need to do is change the area in the array name instead of editing heaps of formulas.

[Beamernsw]

Ok, so I've still been playing. This solution is much better I think.

Formula in cell O2 is:

Please Login or Register  to view this content.

It still uses the helper column A, but you can add or subtract suppliers without it effecting the formula at all.
To add new products, just change the N2 to the new last product column and change the 13 to the number of that column -1.

[Ricci2810]

Cheers Beamer, I had a play around with it with the irl application, but it didn't seem to adapt correctly.

Could you possibly have a look.

Your method is perfectly what I was looking for (Next step would have been VBA coding godforbid), I just can't seem to apply it.

[Beamernsw]

Ok...so...

Formula in cell R5 was almost right. All it needed was the "Rates!" sheet reference added to the indirect function.
=SUMPRODUCT(D5:N5,INDIRECT("Rates!"&ADDRESS(MATCH(B5&$C$2,Rates!A:A,0),4)&":"&ADDRESS(MATCH(B5&$C$2,Rates!A:A,0),14)))

I then added an IFERROR function to prevent it from showing errors on empty lines.
=IFERROR(SUMPRODUCT(D5:N5,INDIRECT("Rates!"&ADDRESS(MATCH(B5&$C$2,Rates!A:A,0),4)&":"&ADDRESS(MATCH(B5&$C$2,Rates!A:A,0),14))),"")

I then replaced the $C$2 references with CELL("contents",INDIRECT(ADDRESS(ROW()-A5-2,3)))
This uses the crew number (assuming they always stay at numbers 1-10) to target the cell containing Day/Night/Weekend. Now you can copy the formula down the page without needing to change the $C$2 to $C$18 and $C$34 etc. It does it for you.

I also noticed that Shift Total in cell D17 can be =SUM(D16:N16) Instead of =D16+E16+F16+G16+H16+I16+J16+K16+L16+M16+N16


Final formula for cell R5:-

Please Login or Register  to view this content.

[Ricci2810]

Has worked perfectly. Last problem is that the products under equipement are not per hour but per a singular product. how would I work that into the sheet?

[Beamernsw]

I'm not sure what the difference is. Are products 1-7 meant to be multiplied by column C (Hours). How about filling in some sample numbers and manually typing your expected result into column R so we can make sure our formula returns your expected results.

[Ricci2810]

Hey Beamer thanks for the quick reply. just solved this myself when I was trying to explain my problem for you. see attached if you wanna get what I mean

[Beamernsw]

Excellent, that's what I was thinking but wasn't sure. Good work.