Excel formulae / VBA help

haz_pro

haz_pro

haz_pro

Associate
Joined
11 Dec 2009
Posts
1,603
Hey all,

I am trying to figure out how to solve the following within Excel either using a fomula or VBA.

The excel is set out as follows (please note this is just a mockup for this thread):

soWuSn2.png


I am trying to have the "Total Cost" for each week calculated automatically. I through I would need some sort of loop using VBA but I can't work out how to do it.

If anyone has any pointers then I would be very grateful.

Thanks.
Harry
 
Thank you for posting.

The actual spreadsheet has around 28 rows on average, with some versions containing hundreds. For this reason I was hoping that a more automated route could be taken rather than having to type out that formula over and over. Any ideas?
 
I'm not 100% sure about this because I haven't got excel on my pc at the minute but if you change those formulae to:

B7 = (B3 * $A$3) + (B4 * $A$4) + (B5 * $A$5)
C7 = (C3 * $A$3) + (C4 * $A$4) + (C5 * $A$5)
D7 = (D3 * $A$3) + (D4 * $A$4) + (D5 * $A$5)

The $ signs tell excel to keep that reference constant so you can drag that formula out and it won't change the reference to the cost cells.
 
On my phone so can't test but think you need the sumproduct formula.

In B7 Something like =sumproduct(A3:A5,B3:B5)
In c7=sumproduct(A3:A5,c3:c5) etc

If not, I'll have another look when I'm on my computer.

Thanks
 
You must log in or register to reply here.
Back
Top Bottom