Multiply two matrix

[van gysel]

I have to compute the costs of a trees plantation
THe planting program is
Year1 : 200 acres Year2: 300 acres year 3 200 acres
my costs are
first year of planting (N0): maintenance 200$/acre, fertlisers 300$/acre harvesting:0
second year N1 maintenance 150$/a, fertilisers 200$ harvesting 50 $/a
I have such data untill N8
I want to know the costs per calendar year, thus multiplying the area planted with its age by the costs corresponding to the age
I built a UDF to solve the problem and it works but I am looking for a neat formula since 10 years I am in the business

 

[Hui]

Van

Firstly, Welcome to the Chandoo.org forums

Can you upload a sample file of what you have?

 

[kchiba]

Hi Van,

Not sure if this is what you are after, note that the harvesting costs refer to the cumulative prior year acres.

cheers

Kanti

 

[van gysel]

Van

Firstly, Welcome to the Chandoo.org forums

Can you upload a sample file of what you have?

 

[NARAYANK991]

Hi Van Gysel ,

Can you check the uploaded file ?

I have introduced the helper calculation for maintenance ; similarly , you can introduce helper calculations for the remaining heads viz. Production , Nursery , Fertilizers and Harvesting ; the totals of all these can then be inserted in the range E25 through L30.

Narayan

 

[van gysel]

Thanks Narayank I consider very helpfull
but is it not possible to have it in one line directly ??

 

[NARAYANK991]

Hi Van Gysel ,

It may be possible , but the formula may be quite complex , since the upper table is in terms of absolute years ( 2014 , 2015 , 2016 ,... ) whereas the lower table is in terms of relative years ( N-1 , N0 , N1 , N2 ,... ) ; as it is the formula is quite lengthy ; putting all of the items together in one formula may make it undecipherable.

If you can wait , probably someone may post it.

I prefer simplicity. If you have an easy-to-understand and modify piece of VBA code , then I would suggest you stick to it.

Narayan

 

[van gysel]

OK thanks
I know it is a tricky problem, I am dealing with it since 15 years and never found an "elegant" answer to it, my UDF works fine but only me can understand it
ONce I inverted the N vectors , it woks better but also very kryptic for a non initiated

 

[Sajan]

Hi Van Gysel,
Here is another approach that does not use any helper cells:

Put the following array formula in cell E29, and copy down and to the right:
=SUM(LOOKUP("N"&IFERROR(1/(1/MMULT(N($E$13:E$17>0),TRANSPOSE(COLUMN($E$13:E$17)^0)))-1,0), $D$20:$M$20, $D21:$M21)*E$13:E$17)

enter with Ctrl + Shift + Enter

Attached is the workbook with the above formula.

Test the formula and data before you apply it to your actual workbook.

(You can make the formula more robust by looking up the row for a cost type, etc. I will leave that as an exercise for you.)

Cheers,
Sajan.

 

[van gysel]

Hi Van Gysel,
Here is another approach that does not use any helper cells:

Put the following array formula in cell E29, and copy down and to the right:
=SUM(LOOKUP("N"&IFERROR(1/(1/MMULT(N($E$13:E$17>0),TRANSPOSE(COLUMN($E$13:E$17)^0)))-1,0), $D$20:$M$20, $D21:$M21)*E$13:E$17)

enter with Ctrl + Shift + Enter

Attached is the workbook with the above formula.

Test the formula and data before you apply it to your actual workbook.

(You can make the formula more robust by looking up the row for a cost type, etc. I will leave that as an exercise for you.)

Cheers,
Sajan.

 

[van gysel]

Thanks Sajan, let me some time to try to make it work in my language setting (French)

 

[SirJB7]

@van gysel
Hi!

If translation is an issue maybe you want to give a look at this:
http://chandoo.org/forum/threads/excel-multilanguage-formula-translator-and-function-reference.4789/

Oherwise you could do this:
a) Select the cell where you want to enter the formula
b) Go to the VBA editor (Alt-F11), immediate window (Ctrl-G if invisible)
c) Type:
Activecell.Formula="<your formula in English here, starting with ="
Only one thing: formula quotes should be doubled, i.e., "" instead of "

Et voilâ, c'est tout!

Regards!

 

[van gysel]

Thanks I am completely happy now

 

[Sajan]

Complete happiness!! That is exactly what we aim for in this forum!!

All kidding aside... thanks for the feedback. Happy to help! Welcome back any time!

-Sajan.

 

[SirJB7]

Hi, van gysel!
Glad you solved it. All credit to Sajan, I just added my two cents. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!