filling a forumla down a column from data across a row

[Guest]

Hi. I guess this problem I have is what pivot tables might be for, but I'm
hoping there's something easier to do when it's a somewhat small amount of
data.

1. First problem:

I have data running left to right (A1, B1, C1, etc.) with a column
represeting each state. I want to drag a formula that is essentially
"=A1/1000" from the top of a new column down to the bottom with each state's
data in row 1 being given this treatment. Natrually, the computer reads this
and fills in A1/1000, A2/1000, A3/1000. I can write A$1/1000, but then each
line just fills in with the result of A1/1000. How do I make the 1 stay the
same, but the Alpha part of the cell name change?


2. I also have data running top to bottom (A1, A2, A3, etc.) with a row for
each state. Thus, there's a diagnol line of data from A1, B2, C3, D4, etc.
where each state is represented in both the column and row. I need to have a
column of data that sums the row (B1:B50), but substracts the relevant cell
in the diagnol. In this case B2. Any thoughts?

Thanks.
-Doug Hess

 

[Frank Kabel]

Hi
use the following formula:
=OFFSET($A$1,0,ROW(1:1)-1)
and drag down

 

[Guest]

1. =OFFSET($A$1,,ROW(1:1)-1)/1000

copied down will increment across

2.

=SUM(OFFSET($A$1,,ROW(1:1),51,))-INDEX($A$1:$AY$51,MATCH(A2,$A$1:$AY$1,0),MATCH(A2,$A$1:$A$51,0))

copied down will increment and sum each column and subtract each intersection
for the same states

Note that this is adapted where the table is A1:AY51 and the data starts in B2
states are in A2:A51 and same states in B1:AY1


Regards,

Peo Sjoblom