Solved

Calculate based upon checkbox and option button selected

Posted on 2014-03-03
14
467 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
  • 9
  • 5
14 Comments
 
LVL 29

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 29

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
 
LVL 29

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 29

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 29

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 29

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 29

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 29

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 29

Expert Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL 2010 7 39
Excel - Macro hide columns not working 4 19
Copy the formula in excel 8 28
VBA in SharePoint 3 17
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now