asked on Wrong formula being used
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
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
Microsoft Excel
Last Comment
Bright01
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,
This formula returns it (and may be copied across and down):
=AVERAGEIF($F$3:$F$27,$I4,
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
=SUMIF(F3:F27,I4,E3:E27)/C
Copy and paste down to K8.
Flyster