Hi!
I am trying to sum monthly unit sales data into quarterly. I have used previous thread and advice in that thread which was SUM(OFFSET('Monthly Sales'!P$3,ROW()*3-9,0,3,1))
Somehow this formula is missing out some data and I dont know why. Because I dont understand the offset function I cannot figure out how to fix the problem. Your advice will be appreciated.
Cheers,
Elmira
Can't tell anything from that, I'm afraid...
Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential information is removed first!!
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I have attached some information.
No, sorry - that's of little or no use. Please attach the workbook where you have tried to use the formula and also include some manually entered expected outcomes.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Bring my apologies if previous table was not clear. In this version I showed the desired values and formulas. Hope it will be more clear. I will appreciate some help pls.
This would work if your data were consistent:
=SUM(OFFSET(K$2,(ROW()*12)-24,0,12,1))
However, you have more rows in Q2 than in the other three quarters, which have 12 each (Q2 is K14:K27). They must have the same number of rows each for this formula to work.
Thank you. I see that I have mostly 13 weeks in every quarter so will I change your formula =SUM(OFFSET(K$2,(ROW()*12)-24,0,12,1)) for the following =SUM(OFFSET(K$2,(ROW()*13)-26,0,13,1))
Moreover, I was adding monthly data and used this formula to find quarterly sales =SUM(OFFSET(B$2,ROW()*3-6,0,3,1)) now if I want to add 4 months would I change the formula =SUM(OFFSET(B$2,ROW()*4-8,0,4,1))
Thank you for your quick response.
I expect so - try it and see.
i tried to adjust both formulas in the same data sheet I have provided but it is not generating anything. Do you know where is my mistake?
=SUM(OFFSET(K2,(ROW()*13)-26,0,13,1))
=SUM(OFFSET(B2,(ROW()*4)-8,0,4,1))
Sorry I see what is my problem, because my cell was in row 12, I should have changed it accordingly.
=SUM(OFFSET(B2,(ROW()*4)-48,0,4,1))
Thank you!
What do you mean "not generating anything"? Pleae attach the adapted workbook where YOU have tried to use them.
EDIT: OK - no problem!
Please mark the thread as solved.
Don't use ROW function for the rows argument in OFFSET because the results will go haywire if rows are added at the top... try add row(s) at cell A1.
Use "12/4*A2-12/4" where 12 = no. of months in a year, 4 = no. of periods in a year and A2 links to the respective month in the year.
You can't add quarterly sales based on weekly sales because weeks may not end on the last day of a month. Take [WE 29 March 2014] as an example, it ends on 29 March and the last 2 days in March go to the following week.
Josephteh, I feel you are saying something really decent and important, but I am having difficulty using your formula. So for this formula to work I should have two reference points, one should be 'value' and second is 'month'. Is my understanding correct. By the way showing solutions on excel helped me a lot! Thank you!
The syntax for OFFSET function is: OFFSET(reference, rows, cols, [height], [width])
In my formula in cell G2, for the rows argument I use: 12/4*A2-12/4
where 12 is the total number of months in a year, i.e. 12
4 is the number of periods in a year, 4 here because there are 4 quarters in a year
For cell H12, where there are 3 periods (of 4 months each) in a year, use 3 instead of 4.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks