Please see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
Thanks in advance
Shaz
OK - I am going to post this beast - but if any Excel Guru wants to improve on it/correct it - PLEASE DO!
The formula below is for row 2. You can copy it down for rows 3, 4, etc.
This seems to be returning the correct values:
I'll leave this formula here so people can learn from my mistakes... I don't recommend using it.
See my formula in post #5 below for a formula without volatile functions!
- MooFormula:
Please Login or Register to view this content.
Last edited by Moo the Dog; 12-11-2012 at 02:54 AM. Reason: See post #5 of this thread for a better formula. Don't use this one!
Thanks heaps Moo, It works wonderfully :-)
Glad to help.
Like I said above, if anyone has another solution, feel free to offer it up.
- Moo
Here is a MUCH better (safer) way of doing this. I knew I would figure it out eventually!
It would probably be a good idea to switch to this formula:You can use that for row 2, then paste/fill down for the other rows.Formula:Please Login or Register to view this content.
It is a non-volatile method. The previous formula used OFFSET -AND- INDIRECT... two volatile functions - not my best work.
Have fun!
- Moo
Not sure whether this...if anyone has another solution, feel free to offer it up
In AY2 cell
Formula:Please Login or Register to view this content.
Please explain me with a file about the expected outcome, if the above formula is not the expected one.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
@Sixth - see my attached modified file (I updated my formula in post #5) The OP wants to sum a 26-week span in each row that terminates at the final value in each row (varies).
I originally proposed a formula using OFFSET and INDIRECT, but knew there was a better (safer) way to do it... and I came up with this:It looks for the cell that is 25 columns to the left of the column holding the row's last value, and sums from that cell, to (and including) the cell with the last value in the row. I hope that makes sense.Formula:Please Login or Register to view this content.
- Moo
@Moo, Thanks for the brief and the file.
Another approach
In AY2 Cell
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks