Excel Question - copying formulas - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to General Talk

Excel Question - copying formulas

Moderator: Baseball Moderators

Excel Question - copying formulas

Postby BeefSandwiches » Wed Jan 18, 2006 2:35 pm

I am no Excel-whiz, so any help is MUCH appreciated...

I created a workbook with worksheets for each position, etc and one sheet containing variables to be used in my projection equations. For example:

Sheet 1 (point values)
Stat 1 = 1 point (in cell B3)
Stat 2 = 2 point (in cell B4)
Stat 3 = 3 points (in cell B5)

Sheet 2 (projections)
PLAYER A
Stat 1 = 25 (in cell B3)
Stat 2 = 50 (in cell C3)
Stat 3 = 75 (in cell D3)

Here is the formula I have to calculate the projected total points for PLAYER A using Sheet 1's point values:

=(Sheet2!B3*Sheet1!B3)+(Sheet2!C3*Sheet1!B4)+(Sheet2!D3*Sheet1!B5)

How do I copy this formula for 100 other rows of players and keep the Sheet 1 cell references constant? How do I copy it across to other worksheets and keep the Sheet 1 cell references constant?

Here is the formula I get when I copy the cell to the next row:

=(Sheet2!B4*Sheet1!B4)+(Sheet2!C4*Sheet1!B5)+(Sheet2!D4*Sheet1!B6)

When the formula I want is:

=(Sheet2!B4*Sheet1!B3)+(Sheet2!C4*Sheet1!B4)+(Sheet2!D4*Sheet1!B4)

Or is there a more efficient way of calculating the values?

Thanks in advance.
BeefSandwiches
Minor League Mentor
Minor League Mentor

User avatar

Posts: 611
Joined: 18 Mar 2004
Home Cafe: Football

Postby mbuser » Wed Jan 18, 2006 2:42 pm

in a formula like this: A1*B1

if you want to keep A1 constant and fill the formula down for all the B's, you mark it as: $A$1

not sure if the same principle applies for multiple sheets, but you could give it a shot
Image
mbuser
Major League Manager
Major League Manager

User avatar
Cafeholic
Posts: 1197
(Past Year: 23)
Joined: 21 Feb 2004
Home Cafe: Basketball

Postby acsguitar » Wed Jan 18, 2006 2:49 pm

God we need a tech support forum!! Come on mods make it happen!
I'm too lazy to make a sig at the moment
acsguitar
Hall of Fame Hero
Hall of Fame Hero

User avatar
Graphics Expert
Posts: 26722
Joined: 7 Apr 2004
Home Cafe: Baseball
Location: Back in General Talk WOOO!!!

Postby Amazinz » Wed Jan 18, 2006 4:18 pm

mbuser wrote:in a formula like this: A1*B1

if you want to keep A1 constant and fill the formula down for all the B's, you mark it as: $A$1

not sure if the same principle applies for multiple sheets, but you could give it a shot


It does. ;-D

Like mbuser said, $ is use it to mark an absolute reference. You can use to mark the column, row or column+row absolute.

$A1 Column
A$1 Row
$A$1 Column+Row
Image
Maine has a good swing for a pitcher but on anything that moves, he has no chance. And if it's a fastball, it has to be up in the zone. Basically, the pitcher has to hit his bat. - Mike Pelfrey
Amazinz
Mod in Retirement
Mod in Retirement

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyeWeb SupporterPick 3 Weekly WinnerSweet 16 SurvivorLucky Ladders Weekly Winner
Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

Postby knapplc » Wed Jan 18, 2006 4:22 pm

acsguitar wrote:God we need a tech support forum!! Come on mods make it happen!

We almost need one dedicated solely to Excel. It's used by so many, and so few are "experts."
Keep wreves in General Talk in 2011!
knapplc
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicGolden Eagle Eye
Posts: 7871
(Past Year: 1)
Joined: 27 Dec 2004
Home Cafe: Football
Location: It's "ell see." ELL SEE!!!

Postby acsguitar » Wed Jan 18, 2006 5:03 pm

Sign my techie form petition!
I'm too lazy to make a sig at the moment
acsguitar
Hall of Fame Hero
Hall of Fame Hero

User avatar
Graphics Expert
Posts: 26722
Joined: 7 Apr 2004
Home Cafe: Baseball
Location: Back in General Talk WOOO!!!

Postby knapplc » Wed Jan 18, 2006 5:06 pm

acsguitar wrote:Sign my techie form petition!


Done! ;-D
Keep wreves in General Talk in 2011!
knapplc
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicGolden Eagle Eye
Posts: 7871
(Past Year: 1)
Joined: 27 Dec 2004
Home Cafe: Football
Location: It's "ell see." ELL SEE!!!

Postby acsguitar » Wed Jan 18, 2006 5:08 pm

knapplc wrote:
acsguitar wrote:Sign my techie form petition!


Done! ;-D


Thanks! I think it would be cool to have it and very helpful...we'll see
I'm too lazy to make a sig at the moment
acsguitar
Hall of Fame Hero
Hall of Fame Hero

User avatar
Graphics Expert
Posts: 26722
Joined: 7 Apr 2004
Home Cafe: Baseball
Location: Back in General Talk WOOO!!!

Postby Big Pimpin » Wed Jan 18, 2006 5:19 pm

The only thing I can add is a couple of shortcuts. For instance, like Amazinz said, you can lock row, column, or both. So you could do the formula in the first cell, lock the row, and then copy that formula across as many columns as you want. Then you can copy it down and it will be how you want it.

Also, the easiest way to lock the reference is to click on the particular cell you want to lock and press F4 until you've got it like you want it (row, column, or specific cell).

And lastly, if you've got a list (say of players) on the left side of what you're doing, you can highlight the row of cells you want copied all the way down, go to the lower right-hand corner, and double click on that and it will copy as far down as your list (of players) goes. Hope that helps. ;-D
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13710
(Past Year: 4)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.


Return to General Talk

Who is online

Users browsing this forum: unioreimi and 4 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Sunday, Oct. 26
(All times are EST, weather icons show forecast for game time)

Kansas City at San Francisco
(8:07 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact