CODEWITHSUNDEEP
excel
Andrei Sevtsenko
Andrei Sevtsenko

Reputation: 223

Excel AVERAGEIFS else statement

I'm trying to perform an AVERAGEIFS formula on some data, but there are 2 possible results and as far as I can tell AVERAGEIFS doesn't deal with that situation.
I basically want to have an ELSE inside it.

At the moment I have 2 ranges of data:

The times on the rows with an 'F' value in column 1 are an hour behind the rest.

I want to take an average of all the times, adjusting for the hour delay on Fridays.

So for example I want it to take an average of all the times, but subtract 1 hour from the values which are in a row with an 'F' value in it.

The way I've been doing it so far is by having 2 separate results for each day, then averaging them again for a final one:

=AVERAGEIFS(G3:G172, B3:B172, "M-T")
=AVERAGEIFS(G3:G172, B3:B172, "F")

I want to combine this into just one result.
The closest I can get is the following:

=AVERAGE(IF(B3:B172="M-T",G3:G172,((G3:G172)-1/24)))

But this doesn't produce the correct result.

Any advice?

Upvotes: 0

Views: 1233

Answers (4)

Andrei Sevtsenko
Andrei Sevtsenko

Reputation: 223

Ended up just combining the two averageifs. No idea why I didn't just do that from the start:

=((AVERAGEIFS(G$3:G171, $B$3:$B171, "F")-1/24)+AVERAGEIFS(G$3:G171, $B$3:$B171, "M-T"))/2

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

If your column of M-T and F is named Day and your column of times is named TIME then:

=SUMPRODUCT(((Day="M-T")*TIME + (Day="F")*(TIME-1/24)))/COUNT(TIME)

Upvotes: 1

skkakkar
skkakkar

Reputation: 2828

Try this

       =(SUMPRODUCT(G3:G172)-(COUNTIF(B3:B172,"=F")/24))/COUNTIF(B3:B172,"<>""""")

Snapshot showing formula on sample data

EDIT Explaining various steps in the formula as per sample data in the snapshot.

  • SUMPRODUCT(G3:G17) sums up all the value from G3 to G17. It gives a value of 4.635416667. This after formatting to [h]:mm gives a value of 111.15
  • OP desires that Friday time be one hour less. So I have kept one hour less for Friday's in the sample data. Similar SUMPRODUCT on H3:H17 leads to a value of 4.510416667. This after formatting to [h]:mm gives a value of 108.15. Which is exactly three hours less for three occurrences of Fridays in the sample data.
  • =COUNTIF(B3:B17,"=F") counts the occurrences of Friday's in the B3:B17 range which are 3 occurrences.Hence 3 hours have to less. These hours are to be represented in terms of 24 hours hence the Function COUNTIF() value is divided by 24. This gives 0.125. Same is the difference of 4.635416667 and 4.510416667 i.e. 0.125
  • Demonstration column H is for illustrative purposes only. Infact Friday accounted values that is 108.15 in sample data has to be divided by total data points to get the AVERAGE. The occurrences of data points are calculated by =COUNTIF(B3:B17,"<>""""") with a check for empty columns.

  • Thus 108:15 divided by 15 data points give 7:13 in the answer.

    Revised EDIT Based upon suggestions by @Tom Sharpe

@TomSharpe has been kind enough to point the shortcomings in the method proposed by me. COUNTIF(B3:B172,"<>""""") gives too many values and is not advised. Instead of it COUNTA(B3:B172) or COUNT(G3:G172) are preferable. Better Formula to get AVERAGE as per his suggestion gives very accurate results and is revised to:

 =AVERAGE(IF(B3:B172="M-T",G3:G172,((G3:G172)-1/24)))

This is an Array Formula. It has to be entered with CSE and further cell to be formatted as time.

Upvotes: 1

xarxziux
xarxziux

Reputation: 371

One simple solution would be to create a separate column that maps the time column and performs the adjustment there. Then average this new column.

Is that an option?

Upvotes: 0

Related Questions