Solved

Wrong formula being used

Posted on 2013-05-19
5
257 Views
Last Modified: 2013-05-19
EE Pros,

I have a "Gap" column that represents the difference between two other columns.  Each Gap has a associated number, 1-5 that is related to one of 3 Categories -- MIX, TODAY, TOMORROW. If we fix one formula, I can fix all the rest.  The formula now uses index and match.  What I need it to do is Index, count the occurances (of each number assigned (i.e. 1-5) and create an average related to the indexed numbers divided by the occurances.  I don't think it does that now as you will see by my sample attached.  I think the formula must be different to get the right result.

Thank you in advance.

B.
INCORRECT-FORMULA.xlsm
0
Comment
Question by:Bright01
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
You can use a combination of sumif/countif. Try this formula in K4:

=SUMIF(F3:F27,I4,E3:E27)/COUNTIF(F3:F27,I4)

Copy and paste down to K8.

Flyster
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
I don't understand why you think the average should be 0.9 for cell K4. There are three instances of a "1" in column F. The corresponding values from column C are 2.4, 2.0 and 1.6. The average of those is 2.0.

This formula returns it (and may be copied across and down):
=AVERAGEIF($F$3:$F$27,$I4,C$3:C$27)
0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 250 total points
Comment Utility
Slight correction here. If you use this formula, it needs these absolute references:

=SUMIF($F$3:$F$27,I4,$E$3:$E$27)/COUNTIF($F$3:$F$27,I4)

(K4 formula)
0
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
Comment Utility
Formulas corrected as previously suggested.
INCORRECT-FORMULAQ28132662.xlsm
0
 

Author Closing Comment

by:Bright01
Comment Utility
Great work guys.  I think both formulas do the job but Byundt's formula is shorter.

Good teamwork.....and thank you.  BTW; Byundt was correct on my mock up...... I had the wrong column identified!  Good catch.... you gave me a great answer anyway.

B.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now