Home |
Search |
Today's Posts |
#1
|
|||
|
|||
offset function
Hi all,
I have a yearly cost in a column, I need to create another column for quarterly cost with the source taken from yearly cost column. Am I right to choose offset funtion in this case and if yes, how to set the correct one (especially regarding the "height" in the function criteria). Any help would be much appreciated. LA |
#2
|
|||
|
|||
Do you want to sum quarterly based on a column of dates and amounts? If so
use =SUMPRODUCT(--(A1:A365=--"2005-01-01"),--(A1:A365<=--"2005-03-31"),B1:B365) for the date range from Jan 1 to Mar 31 -- Regards, Peo Sjoblom "LA" wrote in message ... Hi all, I have a yearly cost in a column, I need to create another column for quarterly cost with the source taken from yearly cost column. Am I right to choose offset funtion in this case and if yes, how to set the correct one (especially regarding the "height" in the function criteria). Any help would be much appreciated. LA |
#3
|
|||
|
|||
As an aside...........what is the difference between using " -- " in
sumproduct formulas, and using asteriks to seperate the criteria instead instead.....? Phil "Peo Sjoblom" wrote: Do you want to sum quarterly based on a column of dates and amounts? If so use =SUMPRODUCT(--(A1:A365=--"2005-01-01"),--(A1:A365<=--"2005-03-31"),B1:B365) for the date range from Jan 1 to Mar 31 -- Regards, Peo Sjoblom "LA" wrote in message ... Hi all, I have a yearly cost in a column, I need to create another column for quarterly cost with the source taken from yearly cost column. Am I right to choose offset funtion in this case and if yes, how to set the correct one (especially regarding the "height" in the function criteria). Any help would be much appreciated. LA |
#4
|
|||
|
|||
I do not think "sumproduct" would help in this case where annual cost is in
one cell for one year, how can I divide it into 4 (4 quarter a year) in the quarterly column. Is there any other posibble function? Tks for your response. "Peo Sjoblom" wrote: Do you want to sum quarterly based on a column of dates and amounts? If so use =SUMPRODUCT(--(A1:A365=--"2005-01-01"),--(A1:A365<=--"2005-03-31"),B1:B365) for the date range from Jan 1 to Mar 31 -- Regards, Peo Sjoblom "LA" wrote in message ... Hi all, I have a yearly cost in a column, I need to create another column for quarterly cost with the source taken from yearly cost column. Am I right to choose offset funtion in this case and if yes, how to set the correct one (especially regarding the "height" in the function criteria). Any help would be much appreciated. LA |
#5
|
|||
|
|||
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation
-- HTH RP (remove nothere from the email address if mailing direct) "Phil Osman" wrote in message ... As an aside...........what is the difference between using " -- " in sumproduct formulas, and using asteriks to seperate the criteria instead instead.....? Phil "Peo Sjoblom" wrote: Do you want to sum quarterly based on a column of dates and amounts? If so use =SUMPRODUCT(--(A1:A365=--"2005-01-01"),--(A1:A365<=--"2005-03-31"),B1:B365) for the date range from Jan 1 to Mar 31 -- Regards, Peo Sjoblom "LA" wrote in message ... Hi all, I have a yearly cost in a column, I need to create another column for quarterly cost with the source taken from yearly cost column. Am I right to choose offset funtion in this case and if yes, how to set the correct one (especially regarding the "height" in the function criteria). Any help would be much appreciated. LA |
#6
|
|||
|
|||
I think you need to give some examples of your data and your expected
results. Other than that it looks like =A1/4 to me, which I am sure is too simplistic. -- HTH RP (remove nothere from the email address if mailing direct) "LA" wrote in message ... I do not think "sumproduct" would help in this case where annual cost is in one cell for one year, how can I divide it into 4 (4 quarter a year) in the quarterly column. Is there any other posibble function? Tks for your response. "Peo Sjoblom" wrote: Do you want to sum quarterly based on a column of dates and amounts? If so use =SUMPRODUCT(--(A1:A365=--"2005-01-01"),--(A1:A365<=--"2005-03-31"),B1:B365) for the date range from Jan 1 to Mar 31 -- Regards, Peo Sjoblom "LA" wrote in message ... Hi all, I have a yearly cost in a column, I need to create another column for quarterly cost with the source taken from yearly cost column. Am I right to choose offset funtion in this case and if yes, how to set the correct one (especially regarding the "height" in the function criteria). Any help would be much appreciated. LA |
#7
|
|||
|
|||
Tks, the scenario is as follow
In Column A (A1:A30), I have annual cost from year 1 to year 30 which is of 30 cells Column B(B1:B120) is quarterly cost which consists of 120 cell (4 quarter a year). Now, I need to use a function to divide each cell in column A into 4 to 4 cells in Column B. Say: B1=A1/4, B2=A1/4, B3=A1/4, B4=A1/4 then (B5:B8)=A2/4 and so on. So in this case, I do not think "sumproduct" would help but "offset" would. But I do not know how to set up a correct formular. And I do not want to divide it manually because in fact, I have much more than just only column A and B. Do you have any idea? "Bob Phillips" wrote: I think you need to give some examples of your data and your expected results. Other than that it looks like =A1/4 to me, which I am sure is too simplistic. -- HTH RP (remove nothere from the email address if mailing direct) "LA" wrote in message ... I do not think "sumproduct" would help in this case where annual cost is in one cell for one year, how can I divide it into 4 (4 quarter a year) in the quarterly column. Is there any other posibble function? Tks for your response. "Peo Sjoblom" wrote: Do you want to sum quarterly based on a column of dates and amounts? If so use =SUMPRODUCT(--(A1:A365=--"2005-01-01"),--(A1:A365<=--"2005-03-31"),B1:B365) for the date range from Jan 1 to Mar 31 -- Regards, Peo Sjoblom "LA" wrote in message ... Hi all, I have a yearly cost in a column, I need to create another column for quarterly cost with the source taken from yearly cost column. Am I right to choose offset funtion in this case and if yes, how to set the correct one (especially regarding the "height" in the function criteria). Any help would be much appreciated. LA |
#8
|
|||
|
|||
"LA" wrote
... annual cost is in one cell for one year, how can I divide it into 4 (4 quarter a year) in the quarterly column Suppose in A2:A3, you have 2 yearly amounts, say: 100 200 and you want the 4 quarter amounts in B2:B9 as: 25 25 25 25 50 50 50 50 Put in B2: =OFFSET($A$2,INT((ROWS($A$1:A1)-1)/4),,)/4 Copy down to B9 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#9
|
|||
|
|||
Think my earlier guess on the set-up was ok <g
Try this .. Put in B1: =OFFSET($A$1,INT((ROWS($A$1:A1)-1)/4),,)/4 Copy down to B120 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LA" wrote in message ... Tks, the scenario is as follow In Column A (A1:A30), I have annual cost from year 1 to year 30 which is of 30 cells Column B(B1:B120) is quarterly cost which consists of 120 cell (4 quarter a year). Now, I need to use a function to divide each cell in column A into 4 to 4 cells in Column B. Say: B1=A1/4, B2=A1/4, B3=A1/4, B4=A1/4 then (B5:B8)=A2/4 and so on. So in this case, I do not think "sumproduct" would help but "offset" would. But I do not know how to set up a correct formular. And I do not want to divide it manually because in fact, I have much more than just only column A and B. Do you have any idea? |
#10
|
|||
|
|||
Great, that's what I need. Thank you very much
"Max" wrote: "LA" wrote ... annual cost is in one cell for one year, how can I divide it into 4 (4 quarter a year) in the quarterly column Suppose in A2:A3, you have 2 yearly amounts, say: 100 200 and you want the 4 quarter amounts in B2:B9 as: 25 25 25 25 50 50 50 50 Put in B2: =OFFSET($A$2,INT((ROWS($A$1:A1)-1)/4),,)/4 Copy down to B9 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#11
|
|||
|
|||
Glad to hear that !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "LA" wrote in message ... Great, that's what I need. Thank you very much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help using the offset function differently | Excel Worksheet Functions | |||
offset and indirect function | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
OFFSET function to pick up monthly groups of data | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |