Apply to next X cells to the right...

[Guest]

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 is>0),

- 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.

 

[Bernie Deitrick]

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