How to count average in certain time range

%3CLINGO-SUB%20id%3D%22lingo-sub-1184266%22%20slang%3D%22en-US%22%3EHow%20to%20count%20average%20in%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184266%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20wizards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20the%20best%20with%20Excel%20so%20I%20would%20like%20to%20ask%20for%20help%20with%20this%20problem%20I%20have.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%208%20hours%20of%20measurement%20data%20and%20I%20want%20to%20know%2030%20minute%20averages%20in%20certain%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20is%20for%20time%20(HH%3Amm)%20and%20B-F%20are%20measurement%20values.%20I%20have%20always%20calculated%20averages%20manually%20in%20each%20time%20frame%2C%20but%20now%20I%20want%20to%20make%20a%20tool%20where%20I%20can%20just%20import%20the%20data%20and%20set%20time%20criteria.%20For%20example%2C%20if%20the%20data%20is%20recorded%20from%2008%3A00%20to%2016%3A00%2C%20and%20I%20want%20to%20know%20the%20average%20at%2012%3A00-12%3A30.%20What%20function%20to%20use%20to%20add%20a%20value%20into%20average%20calculation%20if%20time%20is%20greater%20or%20equal%20to%20start%20time%20(12%3A00)%20and%20lower%20or%20equal%20than%20end%20time%20(12%3A30).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20confusing%20question%20and%20thanks%20for%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1184266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1184431%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20average%20in%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1184431%22%20slang%3D%22en-US%22%3ESuppose%20you%20have%2012%3A00%20in%20cell%20A1%20and%2012%3A30%20in%20B1%20and%20your%20data%20starts%20on%20row%203%3A%3CBR%20%2F%3E%3DAVERAGEIFS(%24B%243%3A%24F%24100%2CA3%3AA100%2C%22%26gt%3B%3D%22%26amp%3BA1%2CA3%3AA100%2C%22%26lt%3B%3D%22%26amp%3BB1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1192697%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20average%20in%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Jan%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20your%20formula%20but%20it%20returns%20%23DIV%2F0!.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20find%20an%20explanation%20for%20this%20but%20I%20can't%20get%20it%20work...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20tips%20would%20be%20highly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rutilusss_0-1582620253017.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173383i8422E667CD0C4F60%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22rutilusss_0-1582620253017.png%22%20alt%3D%22rutilusss_0-1582620253017.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20that%20example%20I%20was%20trying%20to%20calculate%20it%20only%20for%20column%20B.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1192787%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20average%20in%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192787%22%20slang%3D%22en-US%22%3EWhat%20is%20in%20the%20cells%20in%20column%20A%20*precisely*%3F%20If%20you%20change%20their%20number%20format%20to%20general%2C%20what%20do%20they%20show%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1192874%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20average%20in%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20it%20to%20general%20and%20now%20A3-A5%20are%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%2288%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2288%22%3E43871.44392%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43871.4441%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E43871.44427%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1192959%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20average%20in%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564051%22%20target%3D%22_blank%22%3E%40rutilusss%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20it%20now!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20had%20actually%20YYYY%2FMM%2FDD%20HH%3AMM%20but%20only%20time%20was%20visible.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDeleting%20the%20date%20fixed%20it.%20Thanks%20for%20your%20advices!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193083%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20count%20average%20in%20certain%20time%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193083%22%20slang%3D%22en-US%22%3EI%20figured%20as%20much%2C%20great%20you%20were%20able%20to%20solve%20the%20issue.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello Excel wizards,

 

I'm not the best with Excel so I would like to ask for help with this problem I have.

 

I have 8 hours of measurement data and I want to know 30 minute averages in certain times.

 

Column A is for time (HH:mm) and B-F are measurement values. I have always calculated averages manually in each time frame, but now I want to make a tool where I can just import the data and set time criteria. For example, if the data is recorded from 08:00 to 16:00, and I want to know the average at 12:00-12:30. What function to use to add a value into average calculation if time is greater or equal to start time (12:00) and lower or equal than end time (12:30).

 

Sorry for confusing question and thanks for any help!

6 Replies
Highlighted
Suppose you have 12:00 in cell A1 and 12:30 in B1 and your data starts on row 3:
=AVERAGEIFS($B$3:$F$100,A3:A100,">="&A1,A3:A100,"<="&B1)
Highlighted

@Jan Karel Pieterse 

 

Hi Jan,

 

Thanks for your help!

 

I used your formula but it returns #DIV/0!. 

 

I tried to find an explanation for this but I can't get it work...

 

Any tips would be highly appreciated!

 

rutilusss_0-1582620253017.png

 

In that example I was trying to calculate it only for column B. 

 

Highlighted
What is in the cells in column A *precisely*? If you change their number format to general, what do they show?
Highlighted

@Jan Karel Pieterse 

 

I changed it to general and now A3-A5 are:

 

43871.44392
43871.4441
43871.44427
Highlighted

@rutilusss

 

I got it now!

 

Column A had actually YYYY/MM/DD HH:MM but only time was visible. 

 

Deleting the date fixed it. Thanks for your advices!

Highlighted
I figured as much, great you were able to solve the issue.