Calculate based upon checkbox and option button selected
I would like to create a formula (array?) that will calculate values based upon the values associated with a checked box and selected percentage tied to a option button. Attached is a simple version of my spreadsheet without the checkboxes and option buttons.
I would like to replicate the formula in C11 to the first 3 rows of the Income area (11, 12, & 13) and have their value changed by selecting the checkbox and option button. The formula I would like the calculated value in C11 is ((median $ or D2 * median # or B2) * percentage or J7) * number of clients or C9.
I am open to any suggestion or another way to do this.
EE-AUTOMATE.xlsx
I would like to replicate the formula in C11 to the first 3 rows of the Income area (11, 12, & 13) and have their value changed by selecting the checkbox and option button. The formula I would like the calculated value in C11 is ((median $ or D2 * median # or B2) * percentage or J7) * number of clients or C9.
I am open to any suggestion or another way to do this.
EE-AUTOMATE.xlsx
ASKER
Sorry, I would like to click on a percentage and have it apply to one of the check boxes which is a calculation of the # times the $; example is the median check box is checked in Tier 1 (175 x $200), the 0.50% cell is clicked and is applied to the value from 175 x $200. I thought of using an option button for the percentages, but I now do not think it is necessary.
If I am still not clear, please let me know and I will try again...thanks is advance for helping...
EE-AUTOMATE.xlsx
If I am still not clear, please let me know and I will try again...thanks is advance for helping...
EE-AUTOMATE.xlsx
ok and when you do all this that you xplained above you expect the result to be repercuted in the table Row 11 to 13 ???
Like what are we supposed to do Change the formulas there ? and to what ?
Then if you click multiple check boxes what happens there ??
If you simply explain in litterla English what you are trying to achieve then maybe we will better understand this whole thing.
gowflow
Like what are we supposed to do Change the formulas there ? and to what ?
Then if you click multiple check boxes what happens there ??
If you simply explain in litterla English what you are trying to achieve then maybe we will better understand this whole thing.
gowflow
Furtheremore I think reading all this again what you need is:
Option button and NOT Checkboxes !!! as you always have 1 option selected
Either Median or Average or Other and this is achieved with option button.
Checkboxes allow you to choose more than one at the same time but your table allow only for 1 option right ??
based on your reply I can give you the solution.
gowflow
Option button and NOT Checkboxes !!! as you always have 1 option selected
Either Median or Average or Other and this is achieved with option button.
Checkboxes allow you to choose more than one at the same time but your table allow only for 1 option right ??
based on your reply I can give you the solution.
gowflow
ASKER
The results are for the cells in rows 11 to 13 and based upon the median, average or other amounts times the percentage times the number of customers/ clients.
If option buttons are the way to go, then great. In Developer, I see 2 types of option buttons, which do I choose?
Yes, I only want to use 1 value for each tier.
Thank you again, and for your patience.
If option buttons are the way to go, then great. In Developer, I see 2 types of option buttons, which do I choose?
Yes, I only want to use 1 value for each tier.
Thank you again, and for your patience.
Well it seems in VBA neither option nor check box will do it for technical reasons the easiest will be dropdown which I will do but quickly need to know from you the following:
1) Any meaning to the C2, C3 ...G2, G3 .. K2, K3.. items #/ other than an esthetic divider ? like separator ?? or you intent to put something in these cells ?
2) For the future you would have other options than Median, Average, Other ?? like do you plan to increase theses ? if yes what would be the names and where do we put the data ? after others ? Like I need to design it so it include future expansion
gowflow
1) Any meaning to the C2, C3 ...G2, G3 .. K2, K3.. items #/ other than an esthetic divider ? like separator ?? or you intent to put something in these cells ?
2) For the future you would have other options than Median, Average, Other ?? like do you plan to increase theses ? if yes what would be the names and where do we put the data ? after others ? Like I need to design it so it include future expansion
gowflow
ok although you did not reply, I think I got the essence of what you want and it is in the macro attached file and here is how it works.
I allowed myself to make changes to your initial file and sorry if it is not what you want please let me know and I will make the necessary amendments.
1) Activate macros
2) Here is how it works. You have dropdown boxes under each tier you can select from either Median or Average or Other or have it blank by deleting what is showing.
3) it will update the figures in the formulas based on the percentage that you see in Cell A2
4) Cell A2 is changed when you click on any percentage
5) either clicking on a percentage or changing the dropdown will trigger update of the formula.
Let me know
gowflow
EE-AUTOMATE-V01.xlsm
I allowed myself to make changes to your initial file and sorry if it is not what you want please let me know and I will make the necessary amendments.
1) Activate macros
2) Here is how it works. You have dropdown boxes under each tier you can select from either Median or Average or Other or have it blank by deleting what is showing.
3) it will update the figures in the formulas based on the percentage that you see in Cell A2
4) Cell A2 is changed when you click on any percentage
5) either clicking on a percentage or changing the dropdown will trigger update of the formula.
Let me know
gowflow
EE-AUTOMATE-V01.xlsm
ASKER
The #/ are just dividers and have no meaning.
Yes, my thought was to be able to change those values based upon difference data runs
I will not be online for a few hours (I've got to run some errands)...I will try all later today...
Yes, my thought was to be able to change those values based upon difference data runs
I will not be online for a few hours (I've got to run some errands)...I will try all later today...
ok I fine-tuned it so if you also change data in the fields it will update automatically the selected formulas which didn't in the previous version.
gowflow
EE-AUTOMATE-V02.xlsm
gowflow
EE-AUTOMATE-V02.xlsm
ASKER
Thanks again! I updated the fields too and applied your solution to my spreadsheet. Everything works well.
One last question before I close the question, is it complicated to keep the tier's percentage next to the tier so that I can refer to it? In other words, if I selected 0.50% for tier 1, 0.30% for tier 2 and 0.15% for tier 3, each of those percentages would be next to their respective tiers.
One last question before I close the question, is it complicated to keep the tier's percentage next to the tier so that I can refer to it? In other words, if I selected 0.50% for tier 1, 0.30% for tier 2 and 0.15% for tier 3, each of those percentages would be next to their respective tiers.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it...I'll close this one and create a new one...I'll will try to do it first so that I can learn it...
well u can try but if want the solution post the question I give you the solution (as it isready now) you will test it (don't look at the code) and then try to do it will be easier as it is quite complex
gowflow
gowflow
Any news on your endavour ? You did not post a question right ?
gowflow
gowflow
Furtheremore, it is not clear what you want.
Can you please be specific with an example of what you now have and what you would want and will be glad to do it for you.
gowflow