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.