| | | # Weekly sum (5 days by 5 or 7 by 7.) based on dynamic criteria.
Asked Jul 16, 2009, 02:11 AM
—
** 4 Answers** | Hello!
I'd like to make a weekly sum but with a dynamic criteria which I can choose.
I have a column with a formula that gives me negative and positive values. The weekly sum only starts when this column is above or equal 1. I want to sum 5 by 5 days (5 by cells from another page in the spreadsheet) by order of appearance.
Somewhat I could manage a formula to do this but it only works with increasing values (using SMALL formula) but it doesn't work if the third sum of 5 days is less than the second, or the fifth is less than the first... or, etc... because the column used to calculate varies with positive values and negative values, e.g, is has expenses and incomes.
Basically, I want to do a Weekly (choosing n-days) Sum but with the flexibility to choose which day is the starting cell (1st Monday, 1st Tuesday, or the 2nd Friday, 2nd Sunday.. ) and how many days I want to sum ( 2 by 2, 3 by 3, 5 by 5, 7 by 7)...
I hope the images attached (2 examples) can help you to help me to solve this problem...
Many Thanks!
P.S: I don't know how to use macros very well... |
4 Answers
| Software Expert | |
Jul 16, 2009, 05:33 AM
| |
It would be more helpful if you posted sample workbooks with sample data so we could present sample answers back specific to your situation. **METHOD 1: Array formula**
Enter this formula in C4 and confirm it with CTRL-SHIFT-ENTER to activate the array: **=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B$1:$B$30, SMALL(IF(ISNUMBER($B$1:$B$30), ROW($B$1:$B$30), ""), ROWS($A$1:$A1))))**
If the correct first entry appears, then copy that cell down the rest of the data set. If you get a #VALUE error, you didn't press CTRL-SHIFT-ENTER (CSE) so the array is broken. Press F2, then CSE to get it going.
CAVEAT: array formulas are performing a LOT of calculations within a single cell. So if your dataset is large, this approach can/will affect sheet performance, sometime quite harshly.
============ **METHOD 2: Add a helper column**
This technique has the added benefit of not suffering from any performance issues. It does not use array formulas or volatile formulas, so even though it uses a lot of formulas, they don't affect speed.
In an empty column, let's say column F is free, add this:
F4: **"KEY"**
F5: **=IF(ISNUMBER(B5),N(F4)+1,N(F4))**
... copy cell F5 down the dataset and a set of numbers will appear. These incrementing numbers provide an "index" that a simple MATCH() formula can use.
Now, in C5: **=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B:$B, MATCH(ROWS($A$1:$A1), $F:$F, 0)))**
... and copy down. | | | New Member | |
Jul 16, 2009, 04:15 PM
| | Hi JB!
Thanks for your help! It helped me a lot! I chose the second method... and even made some improvements. Many many thanks! It worked like a charm!
:) | | | New Member | |
Jul 16, 2009, 04:18 PM
| | Originally Posted by **JBeaucaire** It would be more helpful if you posted sample workbooks with sample data so we could present sample answers back specific to your situation. **METHOD 1: Array formula**
Enter this formula in C4 and confirm it with CTRL-SHIFT-ENTER to activate the array: **=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B$1:$B$30, SMALL(IF(ISNUMBER($B$1:$B$30), ROW($B$1:$B$30), ""), ROWS($A$1:$A1))))**
If the correct first entry appears, then copy that cell down the rest of the data set. If you get a #VALUE error, you didn't press CTRL-SHIFT-ENTER (CSE) so the array is broken. Press F2, then CSE to get it going.
CAVEAT: array formulas are performing a LOT of calculations within a single cell. So if your dataset is large, this approach can/will affect sheet performance, sometime quite harshly.
============ **METHOD 2: Add a helper column**
This technique has the added benefit of not suffering from any performance issues. It does not use array formulas or volatile formulas, so even though it uses a lot of formulas, they don't affect speed.
In an empty column, let's say column F is free, add this:
F4: **"KEY"**
F5: **=IF(ISNUMBER(B5),N(F4)+1,N(F4))**
...copy cell F5 down the dataset and a set of numbers will appear. These incrementing numbers provide an "index" that a simple MATCH() formula can use.
Now, in C5: **=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B:$B, MATCH(ROWS($A$1:$A1), $F:$F, 0)))**
...and copy down. Hi JB!
Thanks for your help! It helped me a lot! I chose the second method... and even made some improvements. Many many thanks! It worked like a charm!:) | | | Software Expert | |
Jul 17, 2009, 10:48 AM
| |
My pleasure, glad it helped! | | Question Tools | Search this Question | | | ### Add your answer here. ##
Check out some similar questions!
Distribute data among agents based on specified criteria [ 2 Answers ]
Respected Sirs,
Im new to VB :( and desperately in need of this.
I need to distribute loans that are received from different states among agents, in such a way that no single agent receives only one state loan, but the loans(per state) are distributed among all. In case the loans(of a...
Functional-based and activity based costing [ 3 Answers ]
Criado,
My assignment for this week is way above my head, the textbook is not of much help and I need some guidance as I go along. Below is the assignment.
I hope you can help me.
pmonica
Trinity Clinic has identified three activities for daily maternity care: occupancy and feeding, nursing...
'too dynamic'? [ 2 Answers ]
I'm 21 and never had a serious boyfriend(I never really stayed with a guy for longer than a month and never had sex with anyone). I have always been a busy girl, never had that much free time, and it never bothered me before. But it does now, cause I'm in college and have more free time, and also...
Painting Cabinets - Oil based or Water based? [ 1 Answers ]
Going to be painting my oak kitchen cabinets white. I heard that oil based paint would be best, but don't want to deal with the extra mess. Is there anything wrong with using a good acrylic enamel?
View more questions Search |