LinkBack URL
About LinkBacks
Attached excel file is pretty straight forward with what is happening. The function is breaking at Column (A & B). It is averaging the value "10" in column C, but not A & B, why?
aveg ifs 2.PNG
Attached excel file is pretty straight forward with what is happening. The function is breaking at Column (A & B). It is averaging the value "10" in column C, but not A & B, why?
aveg ifs 2.PNG
There is no formula in the attachment - you seem to have copied just values. Please try again.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
It will average if C=10 AND B=9: I suspect this not what you want.
I apologize, her eis the formula and I have updated the excel file with the formula also in cell (U15)
Please Login or Register to view this content.
I still don't see that formula anywhere in the file (U15 or other).
What kind of if logic do you expect? Recognize that, as JohnTopley tried to explain, Excel combines the ifs of average ifs in an and logic -- so that all of the conditions must be true for a given record before Excel will include that entry in the average. I note that none of the records in your sample file have 8 in A and 9 in B and 10 in C. I am guessing that Excel is returning Div/0 for the AVERAGEIFS() because there are no records that meet all criteria.
Perhaps you intended an or logic (average if A is 8 or B is 9 or C is 10)? Or perhaps you intended some other logic? Whatever you intended, it appears that you need to think through the desired logic, then work out a formula or combination of formulas that will correctly represent that logic. It's not clear to me what your desired logic is, so I cannot yet suggest anything.
Originally Posted by shg
I reattached with formula this time. I apologize for that. However the screenshot shows that the conditions are being met for Column (A,B,C)
A= 8
B=9
C=10
I need the averageifs to run based off the criteria in this example. If you test the formula in FX, you can see it working on column C with the condition of "10", then when I try and include column B, condition of "9", it breaks.
@Shruder.... U15 displays a #DIV/0 error. The formula is:
=AVERAGEIFS(L:L,D:D,D2,E:E,E2,F:F,F2,G:G,G2,I:I,I2,T:T,T2,U:U,U2,V:V,V2,C:C,10,B:B,9,A:A,8)
The formula =COUNTIFS(D:D,D2,E:E,E2,F:F,F2,G:G,G2,I:I,I2,T:T,T2,U:U,U2,V:V,V2,C:C,10,B:B,9,A:A,8) returns zero. So there is indeed no row that satisfies all conditions (at the same time).
So AVERAGEIFS is correct to return a #DIV/0 error.
A visual inspection confirms that. At the very least, you require a row where A is 8 and B is 9 and C is 10. I do not see any such row.
That is interesting, I will keep trying other formulas then.
thanks for looking at this
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks