Couple of Formulas for cells and total not surpassing 100% or 1

boldcode

Active Member
Joined
Mar 12, 2010
Messages
347
hello,

the first formula that I want create sums B2:J2 in K2 to 100% or 1 essentially and the total K2 cannot surpasse 100% or 1. The reason I say 100% or 1 is because the user can enter a percent in cells B2:J2 or a number in decimal format that is less than one.

the second formula I want the user to be to enter a percent or a number in decimal format into cells B2:J2.

That's pretty much it.

I really don't need vba code to do this, I figured a couple of formulas can do the trick.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
1: =MIN(1,SUM(B2:J2))

I don't understand what you want for your second formula.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
boldcode,
what values do you want the user to enter in what cells? (i.e. what keystrokes does the user make)
and what do you want the user to see in which cells when they enter those values?
 

boldcode

Active Member
Joined
Mar 12, 2010
Messages
347
HOTPEPPER,

The user will be entering numbers in decimal format greater than "0" but less than "1" or a percent greater than "0" but less than 100%. He will be entering these values starting in Cell B2 to whatever the last cell in column J is. The first formula in the example below goes into K2 and the second formula goes into B2, C2, D2, etc...

For Example:

<table style="width: 995px; height: 60px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="11" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl65" style="width:48pt" width="64">B</td> <td class="xl65" style="width:48pt" width="64">C</td> <td class="xl65" style="width:48pt" width="64">D</td> <td class="xl65" style="width:48pt" width="64">E</td> <td class="xl65" style="width:48pt" width="64">F</td> <td class="xl65" style="width:48pt" width="64">G</td> <td class="xl65" style="width:48pt" width="64">H</td> <td class="xl65" style="width:48pt" width="64">I</td> <td class="xl65" style="width:48pt" width="64">J</td> <td class="xl65" style="width:48pt" width="64">K</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">row 1</td> <td class="xl65">Exp 1</td> <td class="xl65">Exp 2</td> <td class="xl65">Exp 3</td> <td class="xl65">Exp 4</td> <td class="xl65">Exp 5</td> <td class="xl65">Exp 6</td> <td class="xl65">Exp 7</td> <td class="xl65">Exp 8</td> <td class="xl65">Exp 9</td> <td class="xl65">Total</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">row 2</td> <td class="xl65">0.1</td> <td class="xl65">0.2</td> <td class="xl65">0.1</td> <td class="xl66">10.00%</td> <td class="xl66">10.00%</td> <td class="xl66">10.00%</td> <td class="xl65">0.1</td> <td class="xl65">0.1</td> <td class="xl65">0.1</td> <td class="xl65">1</td> </tr> </tbody></table>
 

boldcode

Active Member
Joined
Mar 12, 2010
Messages
347

ADVERTISEMENT

mikerickson,

I responded back to HOTPEPPER with more detail of what I am looking for. Below is what I told him:

The user will be entering numbers in decimal format greater than "0" but less than "1" or a percent greater than "0" but less than 100%. He will be entering these values starting in Cell B2 to whatever the last cell in column J is. The first formula in the example below goes into K2 and the second formula goes into B2, C2, D2, etc...

For Example:

<table style="width: 1159px; height: 60px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="11" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">
</td> <td class="xl65" style="width:48pt" width="64">B</td> <td class="xl65" style="width:48pt" width="64">C</td> <td class="xl65" style="width:48pt" width="64">D</td> <td class="xl65" style="width:48pt" width="64">E</td> <td class="xl65" style="width:48pt" width="64">F</td> <td class="xl65" style="width:48pt" width="64">G</td> <td class="xl65" style="width:48pt" width="64">H</td> <td class="xl65" style="width:48pt" width="64">I</td> <td class="xl65" style="width:48pt" width="64">J</td> <td class="xl65" style="width:48pt" width="64">K</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">row 1</td> <td class="xl65">Exp 1</td> <td class="xl65">Exp 2</td> <td class="xl65">Exp 3</td> <td class="xl65">Exp 4</td> <td class="xl65">Exp 5</td> <td class="xl65">Exp 6</td> <td class="xl65">Exp 7</td> <td class="xl65">Exp 8</td> <td class="xl65">Exp 9</td> <td class="xl65">Total</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">row 2</td> <td class="xl65">0.1</td> <td class="xl65">0.2</td> <td class="xl65">0.1</td> <td class="xl66">10.00%</td> <td class="xl66">10.00%</td> <td class="xl66">10.00%</td> <td class="xl65">0.1</td> <td class="xl65">0.1</td> <td class="xl65">0.1</td> <td class="xl65">1</td> </tr> </tbody></table>
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, you can't have a formula and a value in the cell at the same time.

Are you looking for Data Validation?

If so, just allow any decimal value between 0 and 1
 

boldcode

Active Member
Joined
Mar 12, 2010
Messages
347
HOTPEPPER,

I understand, data validation would be nice but I don't think I know how to do that.

I also modified the first formula into this:

=IF(COUNT(B2:J2),MIN(SUM(B2:J2),100%),"")

which pretty much keeps the total to 100%, the only thing is that I want the user to know he cannot surpass 100%. So he would then have to change some values in B2 through J2 to correct his mistake.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,318
Members
414,441
Latest member
KellyTheKid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top