Formula Question

[jlawrenz]

I have five values in a column from C7 to C11 and I want to multiply them with five values in a row. Which I can do, but then I want to copy them down the row with out changing C7 to C11.

For example, C7 multiplies with B16 and C8 multiplies with C16 then C9 with D16 and so on. Then get the sum of all five multiplication in H16.

I wrote H16 "=(C7*B16)+(C8*C16)+(C9*D16)+(C10*E16)+(C11*F16)" but when I copy and past down one row to H17 I get "=(C8*B17)+(C9*C17)+(C10*D17)+(C11*E17)+(C12*F17) and that's not what I want. I want "=(C7*B17)+(C8*C17)+(C9*D17)+(C10*E17)+(C11*F17) just the row to change and not the column.

Can anyone help?

 

[sulakvea]

hi, and welcome to the board

yes - you can put $ sings (dollar signs) in front of the references that you dont want to change. say - you write $A$1, and copy it down - then it will copy as $A$1 in all cells.

if you only place the dollar sign in front of the column ($A1) - it will change the rows but keep the columns, etc.

plus, your formula can be simplified by using SUMPRODUCT function (look up help on it) in Excel

HTH

 

[Aladin Akyurek]

I have five values in a column from C7 to C11 and I want to multiply them with five values in a row. Which I can do, but then I want to copy them down the row with out changing C7 to C11.

For example, C7 multiplies with B16 and C8 multiplies with C16 then C9 with D16 and so on. Then get the sum of all five multiplication in H16.

I wrote H16 "=(C7*B16)+(C8*C16)+(C9*D16)+(C10*E16)+(C11*F16)" but when I copy and past down one row to H17 I get "=(C8*B17)+(C9*C17)+(C10*D17)+(C11*E17)+(C12*F17) and that's not what I want. I want "=(C7*B17)+(C8*C17)+(C9*D17)+(C10*E17)+(C11*F17) just the row to change and not the column.

Can anyone help?

In H16 enter and copy down:

=(C$7*B16)+(C$8*C16)+(C$9*D16)+(C$10*E16)+(C$11*F16)

 

[jlawrenz]

THANKS WORKED GREAT!

I have more questions if anyone has time. Probably by the time I'm done you will tell me to take a class on MS Excel. But I will give it a shot.

Brief description to help you understand what I'm working on.

I'm a contractor and I've been using excel to do my daily logs, production sheets, and scheduling all with excel, and would like to combined all three into one excel file that works together.

Yes, I know they make software that will do this for me, but I can't afford it!

Question 1: Every daily log will be it's separate sheet and on that sheet there will be a cell where I will put the days date. I would like that sheet to automatically name that sheet as the same thing I put in the "Date" cell.

Question 2: On the daily log sheet I have cell that I put down my daily production. Then I have a production sheet that I will put the same production info on. I would like the log sheet to transfer the info to the production sheet and put it on the same day.

Example: On log sheet I put we laid 2000 brick on 4/25/11. I won't that info to go to my production sheet and put it on 4/25/11's row.

I still have more ideas and questions, but I'll stop and see if anyone can help with these two.

Thanks

 

[rajulu1234]

Dear

Please try =sumif format.

thaks

rajulu
mumbai