You are here:

Excel/IF and Average Formula

Advertisement


Question
QUESTION: Hi Andrea,

I desperately need help.

I am working on monthly sales figures; so i have the sales figures from January to December in columns C1 through to M1 for products. I also have a previous years monthly average sale in column N1.

What i want to do: Get a formula to calculate the averages of column C1:M1 for those cells that have a higher value than N1 divide by 2.
Ive tried AverageIf but returns error :(

Thanks in advance.

Yana

ANSWER: Yana -

The formula is in the questions. You have two questions:
 1. What is the average of columns C1:M1?
 2. Is that average higher than N1/2?

I get the impression that if the average IS higher, you want to display that average. If not, you don't want to display the average.

The formula for the first question is simple: AVERAGE(C1:M1)
The second formula is an IF statement - in English, If the average is higher than N1/2, then display the average; if it is not, then display nothing. As a formula, that looks like:
=IF(AVERAGE(C1:M1)>N1/2,AVERAGE(C1:M1)," ") You can see how the first question fits into the second question.

Please let me know if this is not what you were looking for - Andrea


---------- FOLLOW-UP ----------

QUESTION: Hi Andrea,

Thanks for the prompt response.

I think i was not quite specific with my question.
Actually, i need those cells to be averaged in C1:M1 range that meet the criteria of being greater than N1/2. if any cell has a lower value to N1/2; then it should not be considered when calculating the C1:M1 average.

I hope i've explained more clearly now.

Thanks.

ANSWER: Yana -

I think I do not understand your objective. Which cell(s) are you comparing against N1/2? Right now it appears you want to compare N1/2 against the average of 11 cells (C1:M1).

Tell me what you are trying to accomplish. You said N1 represents the overall average of the previous year. And cells C1 through M1 each represent a month of the current year. So, what information will this new average convey? The average of last year to this year? The average of only the best months? An example of what the data would look like might help clarify.

Looking forward to your information - Andrea



---------- FOLLOW-UP ----------

New Average
New Average  
QUESTION: Hi Andrea,

When stock is trickling in a particular month, the sales reported are much lower than usual. If you dont have stock; obviously you dont sell.

This is the reason why i am using the 2012 average (in column N) which is a quantity that can act as a guideline to not consider the cells for doing the mean, which are less than N1/2.

Please see attached sample sheet; where i have shown what i am doing manually. I need to simplify this with formulas if possible.

Answer
Yana -

Yes, I think I understand now.

And the problem with using AVERAGEIF() is the criteria field doesn't like using ">" and the formula "N1/2" at the same time. So we have to find a way to convince the field that ">N1/2" is only one piece of information.

Use CONCATENATE() to combine the > symbol and the formula; because CONCATENATE technically returns only one piece of data, AVERAGEIF() will accept it. Try:

=AVERAGEIF(C1:M1,CONCATENATE(">",N1/2))

Hope this helps - Andrea
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Andrea Lynch

Expertise

I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.

Experience

I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.

Education/Credentials
BA, English, Western Washington University Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension

Awards and Honors
Rated in the top ten instructors (national), New Horizons Computer Learning Center.

©2016 About.com. All rights reserved.