How to average alternating values across rows that meet a certain criteria

**doubleM3000** · 12-10-2012, 06:23 PM

Hi all,

So I created a spreadsheet that lists "Scheduled" and "Actual" hours in columns for different months of the year. I would like to calculate the total average of "actual" hours for each row. The thing is, I only want to average the "actual" hours that ALSO have a "scheduled" hours of 1.5 beside them. I would like to be able to do this without rearranging the layout of the Spreadsheet (i.e. switching cols and rows).

If this isnt possible, and rearrangement is a must please advise me on the fastest way to rearrange this. The attachment is only a small sample of my MASSIVE spreadsheet. Any advice is appreciated.

**ConneXionLost** · 12-10-2012, 07:03 PM

This works:

Please Login or Register  to view this content.

But it is sensitive to the layout being just as it is.

It probably better demonstrates the reason for an alternate layout.

**doubleM3000** · 12-10-2012, 07:25 PM

Do I copy this to cell Z4 and drag down? If I do that it gives me incorrect values. The average for cell Z4 is supposed to be 0.68 not counting the 0's in the average. Also, what do the "--" do in the formula? thanks

**doubleM3000** · 12-10-2012, 07:34 PM

nevermind, everything else except for the first cell seems to be working fine! so how would I modify this formula to account for the 0's? (so that they arent included in the average). And what do the "--" mean?

Also, can I use the same formula for finding the average of the "actual" hours which have a corresponding "scheduled" hours of 3 as well?

thanks!

**daddylonglegs** · 12-10-2012, 07:36 PM

Try using AVERAGEIFS - this formula in Z4 copied down

=AVERAGEIFS(G4:Y4,G4:Y4,">0",G$3:Y$3,"Actual",F4:X4,1.5)

Observes your other conditions but ignores zeroes in the average.....

**ConneXionLost** · 12-10-2012, 07:44 PM

The formula is counting the number of "Scheduled" hours that equal 1.5 (nine in row 4). It sums the adjacent Actuals, and divides by the count. If you ignore the Actuals that equal zero, then you aren't getting a true average. That said, if you want the average in that manner, try:

Please Login or Register  to view this content.

The "--" is a double unary. It converts logical TRUE / FALSE to 1 / 0 values.

**doubleM3000** · 12-10-2012, 07:49 PM

could you also please explain the term --(NOT(MOD(COLUMN(F5:X5) ?

**ConneXionLost** · 12-10-2012, 07:53 PM

Since you appear to be using Excel 2010, then ddl's solution is a much easier one to work with.

**doubleM3000** · 12-10-2012, 07:58 PM

Yes! Thank you all for your help!

How to average alternating values across rows that meet a certain criteria

LinkBack

Thread Tools

Rate This Thread

Display

How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Re: How to average alternating values across rows that meet a certain criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions