Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Doug
 
Posts: n/a
Default filling a forumla down a column from data across a row

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
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Doug" schrieb im Newsbeitrag
...
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


  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



"Doug" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find rows for unique data in 1 column and different data in other. Dot Majewski Excel Discussion (Misc queries) 1 January 21st 05 01:23 AM
How can I insert a vertical column break between data to create a. Mark Wisdom Excel Worksheet Functions 1 November 23rd 04 05:10 AM
Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 03:09 PM
formula to determine the first column containing any data sd Excel Worksheet Functions 5 November 9th 04 09:06 PM
getting data from 2 excel sheets automatically pinar Excel Worksheet Functions 0 November 9th 04 12:47 PM


All times are GMT +1. The time now is 07:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"