I have the following formula that looks down a column for a specific date and then returns data from the OFFSET. What I need to happen now is for the formula to return the AVERAGE of all the Mondays in the range.
OFFSET(Data!$A$3,(MATCH(VLOOKUP($N$6,Data!$A$3:$V$833,1,FALSE),Data!$A$3:$A$833,0)+24),21)
So basically rather than have VLOOKUP find a specific date, I need to find all the Mondays in the range A3:A833, and then return the AVERAGE of the OFFSET.
Any ideas?
Thanks.
this is set up to look at dates in a4:a21, and data in b4:b21
=SUMPRODUCT((WEEKDAY(A4:A21,2)=1)*(B4:B21))/SUMPRODUCT((WEEKDAY(A4:A21,2)=1)*1)
not a professional, just trying to assist.....
I've tailored the formula below to my specific ranges, but I'm getting an #VALUE error.
Am I doing something wrong?
=SUMPRODUCT((WEEKDAY(Data!A3:A235,2)=1)*(Data!T3:T235))/SUMPRODUCT((WEEKDAY(Data!A3:A235,2)=1)*1)
is there any text in those ranges?
No text. Only formulas.
and the dates are dates, not text, right?
Yes, dates are formatted as dates. If I set the formula to only a single cell that contains Monday, it works. But as soon as I extend the range, I get the error.
=SUMPRODUCT((WEEKDAY(Data!A29:A29,2)=1)*(Data!T29:T29))
try =sumproduct((Data!T3: T235)*1)
if that gives a #value error there is text in that range
Got it!
I had an IF(ISERROR statement setting the cell to "" if the sum of a range of cells was equal to zero.
Took out that statement and viola.
Thanks for all the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks