Previous Topic Next Topic

Excel Formatting

Closed
Bill Tom Posts 1 Registration date Sunday March 10, 2013 Status Member Last seen March 10, 2013 - Mar 10, 2013 at 12:38 AM
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 - Mar 10, 2013 at 03:38 AM
Hello,
I am having trouble with a formula in Excel. I have 600 rows of financial data.
Column A has numbers 1 thru 6, which correspond with different departments.
Columns B and C have dollar values.
I basically need the Average of all the Column C values divided by the Average of all the Column B values ONLY in the rows where the Column A value is 1.
Then I need the Average of all the Column C values divided by the Average of all the Column B values ONLY in the rows where the Column A value is 2.
and so on for 3-6 as well.
By the way, I want to do this WITHOUT sorting the data. I want to be able to enter new data in rows at the bottom without having change any formulas.

1 reply

Answer 1 / 1
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Mar 10, 2013 at 03:38 AM
Hi Bill Tom

Use the AVERAGEIF for your question.

=ROUND(AVERAGEIF(C:C,">0")/AVERAGEIF(A:A,1,B:B),2)

For your criteria in column A. You will be better off referencing another cell. For example D1, where you have data validation set up as a list, Source: "1,2,3,4,5,6" without the quotes

Kevin
0