Hello,
I have columns of data that I apply AVERAGEIFS on with multiple criteria.
Sometimes though, the averange range has text instead of numbers.
I'm trying to have this text reported the same way, not necessarily an "average" of the text, but at least the first occurring text given the criteria.
Does anyone have an idea how to achieve this?
I have attached an example file to demonstrate my intention.
Try: =IFERROR(AVERAGEIFS(C:C,A:A,F3,B:B,H3),LOOKUP(2,1/(A:A=F3)/(B:B=H3),C:C))
Click the * to say thanks.
You can use this in J3:
=IFERROR(AVERAGEIFS(C:C,A:A,F3,B:B,H3),INDEX(C$3:C$18,MATCH(1,(A$3:A$18=F3)*(B$3:B$18=H3),0))) Ctrl Shift Enter
in J3
=IFERROR(AVERAGEIFS(C:C,A:A,F3,B:B,H3),LOOKUP(2,1/((A:A=F3)*(B:B=H3)),C:C))
NOTE: This only works because where you have text e.g. Germs and diploma has the same text and no values
Same with Amoebas and degree.
This will not work properly with a combination of text and numbers, e.g.
Germs diploma dropped out
Germs diploma 550
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thank you guys.
I managed to make 63falcondude's solution work in my actual file.
Happy to help. Thanks for the rep!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks