Solved

Calculate based upon checkbox and option button selected

Posted on 2014-03-03
14
476 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

773 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