Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
find rows for unique data in 1 column and different data in other. | Excel Discussion (Misc queries) | |||
How can I insert a vertical column break between data to create a. | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
formula to determine the first column containing any data | Excel Worksheet Functions | |||
getting data from 2 excel sheets automatically | Excel Worksheet Functions |