[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Calculate based upon checkbox and option button selected

Posted on 2014-03-03
14
Medium Priority
?
510 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 31

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 31

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

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 31

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 31

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 31

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 31

Accepted Solution

by:
gowflow earned 1200 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 31

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 31

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…

649 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