• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiply two matrix

van gysel

New Member
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
 
Van

Firstly, Welcome to the Chandoo.org forums

Can you upload a sample file of what you have?
 
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
 

Attachments

  • ForVan.xlsx
    10.4 KB · Views: 12
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
 

Attachments

  • exemple plantation.xlsx
    12 KB · Views: 12
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
 
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
 
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.
 

Attachments

  • Chandoo-van Gysel-exemple_plantation.xlsx
    16.3 KB · Views: 13
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!

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!
 
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.
 
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!
 
Back
Top