Dear Champs,
I have a table for multiple users to have average of errors grouped for certain activities
The challenge is that when I have multiple names to be selected in one range to get average i wont get it. I have tried to explain the the sample attached.
Capture.PNG
You can use this formula in G14:
=IFERROR(AVERAGEIFS($D:$D,$A:$A,$F14,$B:$B,$G$11,$C:$C,G$12),"")
then copy across and down to fill your table.
You may need to change the commas ( , ) to semicolons ( ; ), depending on your regional settings.
Hope this helps.
Pete
First you will have to create a name manager for TravelClaim as it would be group of client name
O11:O14 putter as
EasyJet
Indigo
KLM
Air Mauritius
Now created name manager and changes dropdown Travel Claims to TravelClaims..
Try
G14Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Check the attached file.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Hi Ankur
Could you please advise me where did you mention about grouping Easyjet,KLM & India as Travel Claims in your formula string
Can you please advise if instead of giving range I can give reference of the entire column A:A As my data is progressive in the table
I get error when i change specified range to column range
For your first question I have created Name Manager Press Alt->M-N and used Indirect function IF($G$11="TravelClaims",INDIRECT($G$11),$G$11).
Second you will have to give range as Sumproduct can't handle entire column, however if you have problem with ranges then you can convert your data in table format which will enable you expandable range.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks