• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Could you point me in the right direction?

Peacedout

Member
Let's say I have the simple spreadsheet I've attached (I actually have a giant spreadsheet with 1000+ "Names", and about 12 "criteria," so is there a limit to the number of criteria you can search for?) I want to find the average Value for all Names that fulfill multiple criteria. (It would also be useful to know how many Names fulfill the specified set of criteria.)

For e.g. in one instance, I might want to find the average Value for all Names that are: Class M, Type SS, Original No. The next time I access the data, I might want to find the average Value for all Names that are: Class C, Type PP, Original Yes.

How would I do this? :) I'm also wondering if I should set up the sheet so that average Values for ALL possible combinations of the criteria are always visible, or whether I should have the User fill the criteria he wants to use each time he uses it. I would put a little shrugging emoji here, but don't know how!
 

Attachments

  • Test.xlsx
    8.8 KB · Views: 8
But then to get the average Value for the filtered data, don't I have to rewrite the formula every time, based on the filters I'm using?
 
Based of Your needs ... some formulas can be there ready.
Your ... sample file has a lot of space right side ...
 
I was hoping NOT to have the user have to access the file that contains the Data (because my REAL file is a GIANT spreadsheet with about 25 years' worth of information), but to have the formulas in a separate spreadsheet that he could access at will.
 
It really depends on how your formulas are set up.

Depending on calculation being done, you can use Subtotal or Aggregate to perform various calculation taking filter into consideration.
 
Peacedout
Based from Your test.xlsx ... which seems to be totally different than in the real life
It's a challenge to give even 99% useful hints.
Now ... You gave an image that users do not cannot see Your sheet
... but those should someway do searches or do filtering.
If You'll use Filtering
... then those formula results should manually fill to Filter.
... and
... as above (#7) ... what should eg Your average should present ( the whole data's ... or ... visible data's)?
If You would like that users could do 'Filtering' without seeing Your data ... that's possible to do too.
 
Thank you, Chihiro and vletm...I appreciate the advice and that it's difficult to understand what I'm hoping to do considering my Test file. I hadn't considered using filtering, myself, until you suggested it. I was assuming I would need a some kind of array formulas to match two or more criteria and return the results, and calculate the average from that.
 
Back
Top