Solved

Calculate based upon checkbox and option button selected

Posted on 2014-03-03
14
494 Views
Last Modified: 2014-03-10
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
0
Comment
Question by:castlerj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 39909300
I just read your post and you refer to 'Income' but do not see any reference to this word in the file you attached.

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
0
 

Author Comment

by:castlerj
ID: 39913862
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
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39914298
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
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 30

Expert Comment

by:gowflow
ID: 39914307
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
0
 

Author Comment

by:castlerj
ID: 39914455
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.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39914504
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
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39914597
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
0
 

Author Comment

by:castlerj
ID: 39914666
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...
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39914750
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
0
 

Author Comment

by:castlerj
ID: 39915869
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.
0
 
LVL 30

Accepted Solution

by:
gowflow earned 300 total points
ID: 39915924
Well then we need to modify this and create for you separate percentages.

If you want post a link to a new question this respect and will be glad to assist. Pls post a link here if you need my help.
gowflow
0
 

Author Comment

by:castlerj
ID: 39915963
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...
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39915969
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
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39917181
Any news on your endavour ? You did not post a question right ?
gowflow
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question