Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Apply to next X cells to the right...
Hi all
I have a form where users enter a quantity into a cell (eg cell D6 = 450,000) and enters a number of weeks into a second cell (eg D1 = 10). I have 52 columns (ie 52 weeks) in columns BA6:CZ6 which I'll call the "thousands grid". I need a formula in each "thousands grid" cell to: - Determine if it's equivalent cell is 0 (eg in range A6:AZ6 it will find that D6 is0), - Go to that equivalent cell in the "thousands grid" (BD6) and calculate the value it found (in this case in cell D6) divided by the number of weeks in cell D1 - then repeat that formula in the next X cells to the right (X = number quoted in D1). So what I'm trying to do is split the value found on the row by the number of weeks entered and report that figure in each individual cell over the period. I hope that makes sense. I can't do a pivot table or anything like that because this is one of many functions occurring on my spreadsheet. Any help would be greatly appreciated as always. -- Thank for your help BeSmart |
#2
|
|||
|
|||
BeSmart,
(I knew a guy named B. Smart, a photographer who went to RIT... any relation? ;-) ) In cell BA6, array-enter this formula (using ctrl-shft-enter), and copy as far to the right (CZ6) as you need. =SUM(IF((COLUMN()=(COLUMN($AZ6)+COLUMN($A$1:$AZ$1 )))*(COLUMN()<=(COLUMN($AZ6)+$A$1:$AZ$1)),$A6:$AZ6/$A$1:$AZ$1)) Watch the line wrapping. This assumes that any value in the first 52 columns needs to be spread out over the corresponding number of weeks in row 1, and summed up in the BA6:CZ6 cells. The formula can be copied down as far as you need. However, you might want to copy it further to the right than CZ6 since some of the values entered in the rightmost data entry cells (say, column BW) may need to be "spread" further to the right.... HTH, Bernie MS Excel MVP "BeSmart" wrote in message ... Hi all I have a form where users enter a quantity into a cell (eg cell D6 = 450,000) and enters a number of weeks into a second cell (eg D1 = 10). I have 52 columns (ie 52 weeks) in columns BA6:CZ6 which I'll call the "thousands grid". I need a formula in each "thousands grid" cell to: - Determine if it's equivalent cell is 0 (eg in range A6:AZ6 it will find that D6 is0), - Go to that equivalent cell in the "thousands grid" (BD6) and calculate the value it found (in this case in cell D6) divided by the number of weeks in cell D1 - then repeat that formula in the next X cells to the right (X = number quoted in D1). So what I'm trying to do is split the value found on the row by the number of weeks entered and report that figure in each individual cell over the period. I hope that makes sense. I can't do a pivot table or anything like that because this is one of many functions occurring on my spreadsheet. Any help would be greatly appreciated as always. -- Thank for your help BeSmart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help adding text values | Excel Worksheet Functions | |||
How do I apply a combo box to multiple cells in Excel so that it . | Excel Discussion (Misc queries) | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
How do I apply a Function to a column of cells and change there va | New Users to Excel |