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

[boldcode]

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.

 

[Scott Huish]

1: =MIN(1,SUM(B2:J2))

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

 

[mikerickson]

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]

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]

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]

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]

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.