Calculations based on checkbox selection

[chinaboy]

Hi guys, I am not sure how to make this work. I would like to have about 20 rows of data. I am trying to make it that when the user selects a checkbox it will populate the increase in Column E. For each row I will have a 10 checkboxes. Only one checkbox can be selected per row. When one is selected, the others should be unchecked.

In Column D will have the salary for each person........Col. E will have the increase amount.......each check box will represent a percentage.

For example....1st checkbox = 2% .....2nd checkbox = 4%.......etc.

So if user select checkbox 1, then it should multiply salary by 2% and place the result in Column E.......If select checkbox 2 then it should multiply salary by 4% and place the reslut in column E.

Can someone please help me with the best way to go about this. Or point me to a working example.

Thanks

 

[bertie]

Hi and welcome to the board.

My first question would be, do you really need checkboxes? The way I would approach this would be to use data validation. Let me explain.

Create a list of Percentages

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 106px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ffff00">%</TD><TD style="COLOR: #ff0000"><= validation List</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2%</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4%</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6%</TD><TD></TD></TR></TBODY></TABLE>

Set up your spreadsheet

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 102px"><COL style="WIDTH: 102px"><COL style="WIDTH: 102px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Salary</TD><TD>New Salary</TD><TD>% Increase</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">10000</TD><TD style="TEXT-ALIGN: right">10200</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2.00%</TD></TR></TBODY></TABLE>

Click inside F2
Click Data => Validation
In the Allow box select List
In the Source box type: =$H$2:$H$4

Now when you click inside F2 a drop down list will appear with the percentages

The formula to update E2 is shown below


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=D2+(D2*F2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Highlight "E2:F2" and drag over the range you need.



Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[chinaboy]

Hi Bertie, and thanks for the welcome. I understand where you are coming from but not sure how that will work in my situation. I don't know how to copy a diagram like you did. That way I you can have a visual of my goal. I will play with it some more or you can explain how to insert a picture.

 

[bertie]

Hi,

The explanation below looks long winded, but after you do this a few times you will wonder what all the fuss was about.


Close Excel
Download and install Excel Jeanie
http://www.excel-jeanie-html.de/html/down_en.php

Open Excel
You will note a menu item added to the right of HELP

Highlight the range you want to display
Click on the ExcelJeanie menu button
Select ExcelJeanie html

At the bottom of the popup window click the Forum Standard button.
This will create an HTML file displaying the range you selected.

Post the Code
==============
On the HTML file click View => Source
Copy the source code and paste into the message box where you post to threads.
Click on Preview post to make sure everything is ok
Click Submit Reply

Error
=======
Sometimes when you preview your post it doesn't look right.
I get around this by copying and pasting the html source code into notepad,
Then copy and paste again into the message box.
I have no idea why this happens.


Excel Jeanie's Help Page
================
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Can't Download Excel Jeanie
==================
If your work situation means you cannot download Excel Jeanie try taking a snapshot of your spreadsheet

On the keyboard press PrintScreen. This copies a screen capture image to your clipboard.
Click Start => All Programs => Accessories => Paint. Opens MSPaint
Ctrl + V. Pastes the screen capture image into MSPaint.
Click File => SaveAs and give the file a .jpg extension (reduces file size).

Upload the file to an image hosting site like http://imageshack.us/
This will generate a link.
Copy and paste the link with your post explaining your problem.

Phew
Bertie

 

[chinaboy]

Ok, hope this works

I will have anywhere between 20 - 30 employees on the spreadsheet. Col. D will have current salary.....Col E will have the increase amount, which is calculate when the user selects the the approriate option button.....Col F will hold the new salary.

Where I have Budjet, is the allotted budjet and after each employee is given a merit selection, this amount should be reduced. For example if Jon Doe gets a 4% increase, then the budjet amount should be reduce to $48,175.

The goal is for the user to be able to distribute merit increase, but also have a visual of the amount left as increase is awarded. They may have to increase or decrease each employee selection. That is why I decided to use the option buttons. I don't want the user to have to type in a percentage, but instead just select and the totals adjust accordingly.

I you see a better way to accomplish this, I am open to anything. I hope I explained it well.

http://i47.tinypic.com/2vxprb6.jpg

 

[bertie]

If you have a max of 30 employeees, each with 10 option buttons, thats 300 option buttons you have take into consideration.

I still think the best way to go is to use a data validation list for the merit increases. See post #2 how to set this up.

In the example below when you click in a cell in column "D" a drop down list of values down column "K" becoems available allowing you to select a merit percentage. Which in turn updates the Increase, New Salary and the Ramaining Budget "I7".

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 99px"><COL style="WIDTH: 22px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 114px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD></TD><TD>Gross Salary</TD><TD>Merit</TD><TD>Increase</TD><TD>New Salary</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00">Merit</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Employee</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>John Doe 1</TD><TD></TD><TD style="TEXT-ALIGN: right">46337</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3.0%</TD><TD style="TEXT-ALIGN: right">1390.11</TD><TD style="TEXT-ALIGN: right">47727.11</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>John Doe 2</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD>Budget Outset</TD><TD style="TEXT-ALIGN: right">50000</TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2.5%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD>Budget Remaining</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">48609.89</TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3.5%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4.5%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5.0%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5.5%</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.0%</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6.0%</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E4</TD><TD>=C4*D4</TD></TR><TR><TD>F4</TD><TD>=+E4+C4</TD></TR><TR><TD>I7</TD><TD>=I6-SUM(E4:E30)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>







If you are havnig trouble putting this together and have no problems with getting files from strangers then send me a pm with your email address and I will forward you the file I am working on.

Bertie

 

[chinaboy]

Bertie,

I like what you have done. I will give it a shot. I think that can work. Will PM you as I think what you have will give me a good start.

Thanks so much.

 

[chinaboy]

Bertie, you are the man. I have re-done the spreadsheet using what you have done as a guide. Works great. In the Increae column, I need to round up or down. I have used the Round function before, but I am not sure how to fit it in with the formula that you used. Can you shed some light?

 

[chinaboy]

Disregard last post, I figured it out. Thanks

 

[chinaboy]

Hi, I have one more problem. Let me know if I need to post a topic for this one. My increase amount and new salary both needs to divisable by 24. The increase amount should also rounded. My current formular for the Increase column is =ROUND(IF(E7=0,0,D7*E7),0)

Again thanks for any help on this.