I have a spreadsheet for my reloading data, but I'd like it to calculate the power factor for me based on the columns. I can do one row at a time with a simple formula, but I'd like to apply it to the whole sheet such that it is automatic. I'm not sure if this is possible.

I have in row one, the title of the column. Such as bullet brand, weight, hi/low/ave velocity, powder type etc.

In my specific example, the bullet weight is in column B, the av velocity is in column J and the PF is in column K.

So if I select cell K2 and enter the formula =B2*J2/1000 then it gives me the power factor in K2.

However, I'd like to have the cells K2:K34 say automatically have the formula added for the appropriate row. It seems like I could do (B2:B34)*(J2:J34)/1000 to format row K and it would just carry through. I can't make it work so I'm not sure if it's possible or if I'm just doing it incorrectly.

I'm not in front of a computer right now so pardon me if this isn't right...

Let's say your sheet has 50 rows plus the column headers.

Row 1 is your column labels
Rows 2-51 are the goods...

Column K in Rows 2-51 is where you want the totals...

Select the cell in column K of Row 2, enter your formula for Row 2 in the formula bar and hit the Enter key, and verify your manually typed formula for R2 works.

Now hover over the cell in R2 column K and grab the little arrow in the bottom corner of that cell and drag straight down, all the way to and including Row 51.

Excel will figure out what you want and copy the formula to K3-51 and automatically adjust the Row details for you.

Does that fix it for you?

quote:

Originally posted by Riley:

Now would it be possible to color code based on values of the same column? That might be asking too much.

Less than 130 would be orange, less than 125 would be red and greater than 130 would be green.

You'd set that up through conditional formatting

Thanks!

quote:

Originally posted by 46and2:
I'm not in front of a computer right now so pardon me if this isn't right...

Let's say your sheet has 50 rows plus the column headers.

Row 1 is your column labels
Rows 2-51 are the goods...

Column K in Rows 2-51 is where you want the totals...

Select the cell in column K of Row 2, enter your formula for Row 2 in the formula bar and hit the Enter key, and verify your manually typed formula for R2 works.

Now hover over the cell in R2 column K and grab the little arrow in the bottom corner of that cell and drag straight down, all the way to and including Row 51.

Excel will figure out what you want and copy the formula to K3-51 and automatically adjust the Row details for you.

Does that fix it for you?

That should work, but you can also double click the lower right corner of the cell that contains the formula, and Excel will automatically fill in the cells below and stop at the last row that has data in it automatically. It's smart enough to know when to stop.

Excellent, thank you. This is what I was trying to achieve and it worked! Use the data carefully etc.

Looks good.

Only thing I’d change, because I’m anal that way, is to format all the numbers in the COL column to be same length, so it would show 1.130 instead of 1.13... but that’s personal preference.

Otherwise, I’d see those numbers and wonder if I forgot another number at the end each time I saw it.