Averaging data if cells in range meet criteria

**gobbledok** · 04-24-2013, 01:49 AM

Hi,

I'm reasonably new to excel and was hoping to get some help with a formula.

Here is some sample data:

Screen Shot 2013-04-24 at 3.39.37 PM.png

What I'm hoping to be able to do is include the hour value in an average calculation if the cell in the previous column meets the "YES" criteria.

So ideally, the average value from the sample data would be 6.

Any help would be appreciated.

Thanks

**benishiryo** · 04-24-2013, 02:00 AM

hi gobbledok, welcome to the forum. try to upload a sample Excel file next time. it's easier for us to test out. assuming data is from A2:J2, try:
=AVERAGEIF(A2:I2,"yes",B2:J2)

notice i intentionally start from A2 & end in I2 for range, but start from B2 & end in J2 for average_range.

**gobbledok** · 04-24-2013, 02:11 AM

Thanks benishiryo!

Apologies for the incorrect file attachment, I'll remember that for next time!

I assume that by starting the average_range one column across from the range it does so for the entire selection? Would it then also work the same way if I was to start the range from A2 and the average_range from D2?

**benishiryo** · 04-24-2013, 02:28 AM

you're welcome. not really sure what you mean. but what it does is average the nth cell when the nth cell reads "yes". so if A2 (1st cell for range) reads "yes", it will average B2 (1st cell for average_range).

so if your average_range starts from D2, then by all means do it. just ensure both ranges (range & average range) have same number of cells. if you start from A2:E2 for range, average_range would be D2:H2. do mark it as "Solved" if that does it. otherwise, attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic