What is the difference between 'J Offer Issued' and 'S offer Issued?'
Hi everyone,
Hope someone can help me with a formula. I am looking for a formula to put into sheet 1 that will average column I in sheet2 (averaging only numbers that are 10 or below) that appear between two dates (in sheet1 - month start/end dates, weekly mon/sun dates), I have tried several different types of formulas I have searched for online without any luck.
I have attached a small sample file of my data.
Hope someone can help, let me know if I dont make any sense - really sorry in advance this is my first time on these forums
What is the difference between 'J Offer Issued' and 'S offer Issued?'
Using your date ranges, try in B2:
=IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$G$2:$G$1142,">="&H2,Sheet2!$H$2:$H$1142,"<="&H3,Sheet2!$I$2:$I$1142,"<=10"),"")
copied across
and in B7:
=IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$G$2:$G$1142,">="&H6,Sheet2!$H$2:$H$1142,"<="&H7,Sheet2!$I$2:$I$1142,"<=10"),"")
copied across
OMG NBVC I think your the best! I changed the portion of the formula where you had G$2 to H$2, H colum would be the column that has the dates we would be looking at (sent dates) and the numbers showed up! Yeey thank you so much! Really really appreciate it!
Quick question, the date ranges, is that inclusive or only between those two dates?
Last edited by TTTNT; 2013-08-20 at 10:24 PM.
the dates are inclusive. (i.e >= means greater than or equal to and <= means less than or equal to). If you don't want inclusive remove the = signs.
Quick question referring to your formula, is it possible to add a text criteria also? like, average all turnaround time for all the ones that say 'package' (in Sheet2 - Column E) that fall between criteria dates?
May need to know what the average turnaround time is for all packaged and standard items between those dates (no more than 10 days out)
Yes, you can
e.g.
=IFERROR(AVERAGEIFS(Sheet2!$I$2:$I$1142,Sheet2!$E$2:$E$1142,"package",Sheet2!$G$2:$G$1142,">="&H2,Sheet2!$H$2:$H$1142,"<="&H3,Sheet2!$I$2:$I$1142,"<=10"),"")
Bookmarks