Hi gurus!
After searching on net I found some ugly formulas to average numbers (excluding blanks and zeros) combined with VLOOKUP. Actually my needs are bit complicated as I have 3 criteria to be looked at:
- Product
- Date
- Average last seven days from today
Is it possible at all? I am sure someone will come up with bright ideas. See dummy...
Thanks a ton guys!
Last edited by ABSTRAKTUS; 06-21-2011 at 05:07 PM.
Hi Abstraktus,
Do you want the average of the last seven days from Today, or from the date in cell G2? Also, based on your criteria, what would you expect the answers to be for Products 7, 2, 13 and 1 in your example?
Also, please can you confirm that you explicitly need zeros to be excluded from the average. I ask because there are no zeros in your example data.
Hi Paul and thanks for a quick reply.
The formula should give me average of the last seven days from the date G2. And the answers I expect for those products should be average in period 14 june between 20 june.
Yes, there are lots of zeros in the orginal workbook. These to be excluded.
Here's one way. This formula in C7 and then copied down:
Please Login or Register to view this content.
forgot to mention that formula needs to be compatible with XL 2003.
Try this array formula. CTRL+SHIFT+ENTER, not just ENTER.
Please Login or Register to view this content.
Thanks. Exactly what I want. Spot on!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks