• 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
 

Hui

Excel Ninja
Staff member
Van

Firstly, Welcome to the Chandoo.org forums

Can you upload a sample file of what you have?
 

kchiba

Active Member
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

NARAYANK991

Excel Ninja
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

NARAYANK991

Excel Ninja
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

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

Excel Ninja
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

van gysel

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

SirJB7

Excel Rōnin
@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!
 

Sajan

Excel Ninja
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

Excel Rōnin
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!
 
Top