Jump to content


Photo

Multiple Averages with If

If Ave

  • Please log in to reply
1 reply to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 30 August 2017 - 04:36 PM

I would like to average one group of cells if the average of another meets a certain constraint. I have a sample grid of numbers in the attachment.

 

I would like to write a formula that would test the average of the cells in the pink box and then average, or sum the cells in the tan box if the constraint on the pink box is met and I can't get the syntax correct. Can you help me out?

 

JPK1066

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 846 posts

Posted 30 August 2017 - 07:05 PM

Hi JPK,

 

Your attachment didn't contain any colored boxes so I'm not able to provide an exact answer, but I think you should be able to do this using the SUM, AVERAGE and IF functions.

 

If you're having difficulty with the syntax, it may help to split the calculation into multiple, small formulas.

 

For the sake of example, let's say you want to SUM the cells in column H and, if the result is over 1000 return the SUM of the cells in column I, or if the result is under 1000 return the AVERAGE of the cells in column J.

 

To do this, you could SUM column H with: =SUM(H4:H16)

...then SUM column I with: =SUM(I4:I16)

The AVERAGE is another simple formula: =AVERAGE(J4:J16)

 

Now that you have all of the relevant values it's relatively easy to put them together with an IF function. Let's assume the previous formulas are in cells A1, A2 and A3:

=IF(A1>1000,A2,A3)

 

You could, of course, put these together into this more complex formula:

=IF(SUM(H4:H16)>1000,SUM(I4:I16),AVERAGE(J4:J16))

 

...but this is not as easy to understand and potentially harder to write and maintain.

 

You can see more about the IF function in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

For more on the SUM function, see: Expert Skills Lesson 3-2 Create an Excel SUM function using Formula AutoComplete.

For more about the AVERAGE function, see the video tutorial: Essential Skills Lesson 2-11 Use AutoSum to quickly calculate averages.

 

I hope this is the answer you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users