• 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.

Changing Values Based on Many Selections

K

kpfluege

New Member
I am creating a spreadsheet with many values that are calculated using mostly averageif, and referencing other large tables within the workbook. I have added two "drill-downs" and two additional criteria selections (yes/no) that I would like the user to be able to customize.

How should I go about making the data reflect the changes made in the drill-downs and criteria selections? The only changing aspect would be which part of the data is averaged in the averageif function.

As of now, I only have the two yes/no criteria filters, and all of the cells are set up with long if statements, but adding in the two additional filters, I feel this is probably long and not very fast. Is there a better way to accomplish this with VBA?
 
Last edited:
Sorry. I've attached a very very simplified version of what I am doing, as well as the table I am using. I can't share data, so I apologize for that. I have several of these little tables with similar calculations that all need to be changed based on the four cells at the top and they're all based off large sets of data that look like the table on the second tab.

I included the formula I used the first time I created the dashboard, which altered the data based on the two yes/no threshold selections, but I feel like recreating this for every cell, and all four criteria is too slow and awkward.
 

Attachments

  • Blank Dashboard.xlsx
    102.2 KB · Views: 6
Personally, I'd recommend flattening your table. Instead of having it in cross tab structure (i.e. have xxx1~4 in rows instead of in columns).

That'd simplify calculation down stream.

Otherwise, I'd set up out of way cell to assign number to E4 & E5 conditions.
Ex: in P1
=IF(($E$4="no")*($E$5="no"),1,IF(($E$4="no")*($E$5="yes"),2,IF(($E$4="yes")*($E$5="no"),3,4)))

Then use
=CHOOSE(P1,Formula1,Formula2,Formula3,Formula4)
 
Using this method, how do I account for the other selections? I might be confused about the use of Formula1, Formula2, etc.
 
So what it does, depending on condition met, P1 would evaluate to 1 to 4.

Choose() is used to select formula for calculation based on 1 to 4.
Either use named range for formula, or replace formula1 with your first formula (i.e. AVERAGE(Table10[Outcome 1 O/E])
 
Okay, that makes more sense, thank you! My only remaining question is how do I account for the other two selections in cells B1 and B2 with this method? It seems like this is adding a lot of potential combinations to the situation?
 
You must log in or register to reply here.
Back
Top