?
Solved

Wrong formula being used

Posted on 2013-05-19
5
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39179356
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 81

Expert Comment

by:byundt
ID: 39179357
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 1000 total points
ID: 39179429
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 81

Accepted Solution

by:
byundt earned 1000 total points
ID: 39179437
Formulas corrected as previously suggested.
INCORRECT-FORMULAQ28132662.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 39179927
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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