PDA

View Full Version : Excel question


Lurker the Second
04-11-2006, 14:24:18
I don't know a thing about Excel and am trying to make a spreadsheet. How do I get it do do the following:

Say I have 100 different items I want to manufacture. Each item contains some or all of up to 8 different components. The amount of each component required will vary from item to item. I want the spreadsheet to calculate the manufacturing cost of each item based on the component costs I input.

I have set the rows and columns up as follows:

Cells B2 through I2 are the cost to me of one unit of each of the components B through I.

Cells B3 through I3 are the amount of each component required to manufacture Item 3.

Cells B4 through I4 are the amount of each component required to manufacture Item 4.

And so on.

I tried to create a function that would set forth in each cell "J" the total cost of manufacturing each of the various items. Using the help function, it seemed to me the SUMPRODUCT function should work, but I think I'm confused about arrays. Anyway, I can't seem to make it work.

It seems to me this should be basic Excel, but damned if I can figure it out.

Scud Wallaby
04-11-2006, 15:20:12
Think it's =SUM(B2:I2)

Then =SUM(B3:I3)

if i've read the problem correctly.

mr_G
04-11-2006, 15:40:40
:lol: you non geek you

Scabrous Birdseed
04-11-2006, 16:24:29
So =(b$2*b3+c$2*c3+d$2*d3+e$2*e3+f$2*f3+g$2*g3+h$2*h3 +i$2*i3)

is too normal a way to do it?

Lurker the Second
04-11-2006, 16:52:33
Hmm, I don't think I've explained things well enough. Scud's solution, if I understand it correctly, will simply add up the numbers in cells B2 through I2 and then do the same thing for cells B3 through I3.

Scabby's works fine, but isn't there a way I can avoid having to input that formula for each item I want to manufacture? In other words, that's ok for item 1 b/c it multiplies cell b2 by cell b3 for item 3. For item 4, though, I need to multiply b2 by b4, c2 by c4,etc and add those results up. Hope that's clearer.

Scud Wallaby
04-11-2006, 16:55:54
Well Scabby's should be OK because he's included absolute cell references - the dollar signs. This means you can copy and paste the formula in J3 to J4, J5 etc.

Scabrous Birdseed
04-11-2006, 17:14:46
Yup - dollar signs in excel makes references absolute. Just input that in the first square, then extend downwards, and the 2 shoud stay the same.

Scud Wallaby
04-11-2006, 17:41:49
This should work too (I think);

=SUMPRODUCT($B$2:$I$2,B3:I3)

and can be copied and pasted down.

C.G.B. Spender
04-11-2006, 20:50:46
Best thread evah

Lurker the Second
05-11-2006, 14:30:12
Something's not working. Scabby's formula calculates the correct amount for the first item, but the result is the same for every other item. Basically what I said in my last post. Do I need to define what the dollar sign does in some way?

Scud Wallaby
05-11-2006, 14:49:43
If you've got it set up like this;

Row 1 - Components
Row 2 - Cost of Components
Row 3 - Number of Components for Item 1
Row 4 - Number of Components for Item 2
Row 5 - Number of Components for Item 3
etc

Then in cell J3, enter this formula;

=SUMPRODUCT($B$2:$I$2,B3:I3)

Click cell J3 and you should see that in the bottom right hand corner of the cell, there is a tiny black box.

When you move your mouse over it, it'll turn into a black cross.

Click it and drag your mouse down. This will copy the formula down but it should copy like this

Cell J3 - =SUMPRODUCT($B$2:$I$2,B3:I3)
Cell J4 - =SUMPRODUCT($B$2:$I$2,B4:I4)
Cell J5 - =SUMPRODUCT($B$2:$I$2,B5:I5)

The dollar signs 'cement' the part of the formula they're put in, so that when copying it down, the references without the dollar signs will change depending on the cell they're in, but the formula will always firstly relate to $B$2 to $I$2

Hope that makes sense - been up all night on caffeine and geek adreneline waiting for a response

Lurker the Second
05-11-2006, 22:35:06
Ah, worked perfectly. Thank you both.

Funko
06-11-2006, 11:09:01
So this is how you get Scud to post.

MoSe
06-11-2006, 15:46:44
and thinking that I got fat earning my living and surplus by teaching such things for years.....