
Create An Ifthen Formula In Excel To Limit Column Total?


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Create An Ifthen Formula In Excel To Limit Column Total?  Excel

View Answers


I want to limit a column total to 100 percent if the column adds up to
greater than 100 percent. Is there an ifthen statement for Excel?
Similar Excel Video Tutorials
3 Formulas For Frequency Distribution
 Excelisfun is conducting a FAVORITE FORMULA SURVEY! Watch the 3 different Formulas in this video and then vote in the comment section for the one you ...
Avoid Data Validations 1 Rule Limit
 See how to work around Data Validations one rule limit by creating a second shrinking list that looses values as they are used in the Data Validation ...
Avoid Data Validation 1 Rule Limit
 See how to work around Data Validations one rule limit by creating a second shrinking list that looses values as they are used in the Data Validation ...
Similar Topics
Hello
I am have problem convert this formula to access
=IF( C4=MAX( $C$2:$C$11 ),C4,C4/100*( $D$1MAX( $C$2:$C$11 ) ) )
Does this look right
Total from form
Expr1: IIf([Percent]=Max([Percent]),[Percent],[Percent]/100*([Forms]![searchForm]![T5]Max([Percent])))
Thanks
I have a pivot table which uses subtotals for rows as well as columns. One field (of 4) is in % format. What I need (and can't figure out how to do) is for this field to have the row Total (xaxis) displayed as a SUM and the column subtotal (yaxis) to display as AVERAGE. It seems that when I change one the other automatically changes as well!
Is it possible to have the total on the xaxis as a Sum Total while having the total on the yaxis (for the same field) as an Average Total? For Example:
Rows (xaxis)
Percent
The field "Percent" is summarized by SUM, so for each day of the week (column field) each item has % displayed. The "Total" (xaxis) sums each of these daily Percent and displays a SUM Total Percent for each item. So far so good...
Columns (yaxis)
Days of the Week
Subtotals are displayed for each day of the week (yaxis)  including Percent  as there are multiple items. Currently, the "Percent" subtotal adds each percent and displays a SUM total  just as the Total on the xaxis does. I need to keep the xaxis Total as a SUM but change the subtotal (yaxis) to AVERAGE because I want the daily average percentage (not the summed average).
Any suggestions?
Hello,
I am trying to create a macro/function that will help get me to my end result, but I have having a tough time getting it to work.
Here is the table I am working with:
QTY
RATE
HOURS
PERCENT
24
3.2
3
250.00%
13
4.5
3
96.30%
TOTAL
6.5
I would like to have a macro/function that can make all cells the PERCENT column as close to being equal to each other as possible by changing the cells HOURS column, while the sum of the HOURS column not being greater than the TOTAL. The formula in the PERCENT column is =IFERROR(A2/(B2*C2),""). I would like to have the macro/function be variable to the number of rows in the table, as I sometimes I am working with 2 rows, sometimes 10.
Thanks!
I am still pretty new to excel. I am working on a sheet that i have a total revenue, cost of sale, then total. i have total rev  COS= total. but now i need to take that to a percentage.
So EI... total rev 100000, COS 70000, total 30000
so the total is 30%
what is the formula to make that percent show up (in this case) e2
hope that makes sense
Hi,
I am working on a bonus spreadsheet...I want to calculate a percentage of salary(column E) times 2 percent if column F is greater than X, then add 2 more percent if Column G is greater than X, then add 2 more percent if column H is greater than X....
Not sure to use Countif or Sumif
this didnt work....
=SUM(F2>"a",E2*2%)+(G2>a,E2*2%)+(H2>a,E2*2%)
Any help is GREATLY appreciated...
BAMA
I have a user that is trying to calculate a running total or percent changes
for the year. We have already calculated the daily percent change for each
day, and now they want to have a running daily total of the yearly percent
change as well as an actual total of the percent that has changed. Any help
would be appreciated.

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200512/1
Hello
Is there a way figure out the percent of a value cell?
For ex what percent is amount from total
Thanks
Jonathan
Total
Amount
Percent
1760.64
105.6384
6
30
0.3
1
12.43
0.7458
6
I just started learning Excel and I'm struggling with a formula that I'm trying to create, to compute Arkansas (5.125 percent) & County (2 percent) sales tax for monthly statements.
I know the formula must accomplish the following:
If amount of total sales is greater than $2,500, then tax equals total sales times 5.125% plus $50 (maximum county tax). If amount of total sales is equal to or less than $2,500, then sales tax equals total sales times 7.125%.
Also, I'm wondering if the formula (once it's created) can be stored as a macro, so that you can enter it quickly into any needed cell down the line. Wouldn't I have to name some cells in each statement in order for this to work, such as 'total sales'? You can see I'm just a beginner and don't know much, but I feel certain there are plenty of 'small business' folks who need this formula as much as I do.
I know Excel is a great program, but I'm about ready to go back to my old calculator! Can anyone out there help me?
Thanks a bunch,
Hazel Williams
Hello, I am trying to create the first standard deviation of a series of numbers starting from the modal value. I have a method to accomplish this but lack the technical excel expertise to do it. Can anyone please examine this and help me to create a formula to obtain the upper and lower limits of the first standard deviation using the method highlighted below?
* From AB29 downwards I have numbered values going from max to min, from AC29 I have the number of counts for each value in AC29.
The Method is :
1) Sum up the total number of counts from AC29. Output this result in S31.
2) Take 68% or 70% of this total number of counts and output this count result to S32, the percent value(68%,70%) is typed S33 and this percent value value can change.
3) Identify the Modal Value which is already calculated by a formula in cell S29.
4) Add The counts of 2 numbers above and 2 below the modal value, meaning add the counts of the 2 values above the modal value then add the counts of the 2 values below the modal value.
5) The counts of the pair of numbers with the greater number should be added to the counts of the modal value.
We continue this procedure until the total number of counts reaches the percentage value stated in point number 2.
In outputting the result we get the upper number and lower number limits of the 1st standard deviation counting from the modal value. the upper limit number would be outputted to cell S35 and the lower limit number would be outputted to cell S36.
Here is an example with the expected result :
# C
1083 1
1082 2
1081 2
1080 4
1079 4
1078 4
1077 4
1076 5
1075 7
1074 9
1073 11
1072 10
1071 10
1070 9
1069 9
1068 8
1067 6
1066 6
1065 3
1064 3
1063 3
1062 2
1061 2
1060 2
1059 2
1058 2
1048 1
Total Counts = 131
70% of total counts = 91.7
Counts of the modal Value = 11
Pair counts : 11 + 20 + 18 + 16 + 14 + 9 + 6 = 94
Upper standard deviation limit number: 1080
Lower standard deviation limit number: 1074
Hope someone can help me out in this.
thanks.
this is what i'm wanting to accomplish
CostX=D4
Quantity=C4
Percent=E4
Total=F4
I need CostX multiplied by a Percent, and that total added to CostX, then multiplied by the Quantity. Is there any way to get this total in one cell? Also is there a way to change the percent amount in the cell itself, without having to adjust the formula, for example if my percent is 15, and I want to bump it up to 30. Thanks in advance, and sorry if this is a stupid question I'm quite new to excel.
I need to increase the numerator of a percent to a new percent by a numercial increase. For example, I need to increase 45/204 (22%) to 29%. Of course if you increase 45 to 29% by adding 14 (45*29%) to it, it also impacts the total in the denominator and only increases the percent to 27%. I know the real answer is 20. 45+20/204+20 = 29% but how do you write this in a formula. Thanks.
When you key in a percent say 91%94% in a column, I want the total at the end to ADD $50. If the percent is 95%100% I need it to ADD $100 to the total column. And if the percent is 0%90%, I need it to SUBTRACT $50 from the total column.
This may sound simple, and I was hoping I could find the answer thru searching the forum but its giving me limited results (im still getting used to the new layout!) Anyway, there are two formulas that I need. One formula is getting the dollar amount of the percent given: for ex:
column A has the cost ($15.95), column b is the percent (8%). column c = is where i want the dollar amount of 8% of 15.95
problem no. 2
instead of the percent provided, it is the dollar amount, in column c I need the percent of the dollar amount given.
ex:
column A ($6.94), column b is .14 what is the equivalent percent of .14 in 6.94
Hope I didn't confuse you guys. Id really appreciate any help
Without using VBA, can anyone tell me how to compute a percent of column for multiple subtotaled items? When I try to use "percent of column" to do this, Excel is trying to use the grand total vs. the subtotal for each item.
Example:
Hattisburg
1316
2
416
3
225
4
196
5
145
6
126
7
84
8
48
9
18
10
10
15
15
16
16
17
17
Tupelo
5516
2
728
3
585
4
412
5
290
6
330
7
294
8
256
9
261
10
210
11
198
12
180
13
130
14
154
15
135
16
112
17
102
18
144
19
95
20
40
21
63
22
66
23
69
24
72
25
25
26
26
27
54
28
112
29
87
30
120
31
93
32
32
41
41
Picayune
3692
Thanks!
Freitag
Using a spreadsheet( like Excel, for example) evaluate your options for a $12000 car. Compare the payments( use the = pmt function in excel), the total amount of interest, and the total you'll pay for the car under the following four options:
a. 3 years at 0 percent
b. 2 years at 1.99 percent annual percent rate( APR)
c. 4 years at 5 percent APR
d. 6 years at 6 percent APR
What other considerations would you take into account if you were going to buy a new car? Are there considerations other than the interest rate and other parts that can be calculated? What are they? How is a car different from other purchases, such as CD's or Tv sets or computers?
I am trying to create an IF statement that has two possible calculations rather than two values that should be applied.
In my workbook I have the following columns: Forecast, Actual, Variance, Variance percent.
The forecast column contains a manually entered number as does the actual column.
The variance column = actual divided by forecast.
For the variance percent column what I want to say is: IF forecast is greater than zero then calculate percent by dividing the variance by the forecast BUT if forecast = zero then calculate variance percent by dividing the variance by the actual.
Sarah
There is probably an easy answer to this,,, but I sure can't figure it out. How do I set up a formula that I can repeat in Column C that is the percent of the Subtotal line for each row in the set.
My forumula in C1 is =B1/B3. C2 is B2/B3. C3 is B3/B3.
I need the formula in C4 to be =B4/B7,,, but how do I get the formula to autopopulate by copying the formula from C1?
A...............B..........C
Bob............10........20%
Bob............40........80%
Bob Total ....50........100%
Ann............20........18%
Ann............30........27%
Ann............60........55%
Ann Total...110.......100%
Thanks for your assistance!
Hi guys,
I know that excel has a row limit of around 65000 butu i was wondering if anyone knew the limit of how many columns excel can store?
I was looking to create a very basic attendance sheet which required a daily column, hence i needed 365 columns (plus a few totals & sums columns =375 columns max in total) but i don't seem to be able to create this many columns.
Is 375 too many columns? Is there a way of expanding the limit number for columns within a worksheet?
Regards,
Shuja
I am trying to hide rows in a sheet once the end of data is reached. I have code already setup to hide the row if Column A contains "H". The end of data is marked by the data in Column E being "Percent of Total Expiring". In column A I have the following formula so if the Column E data is blank or text, it returns "S" for show the row. When the logical_test is met it returns "X" to signify the end of data.
=IF(E19="Percent Of Total Expiring","X","S")
(E19 just happens to contain "Percent Of Total Expiring" on the table I am working with)
I want to amend this formula so if the cell above (in Column A) contains "X" or "H", then the cell value will be "H". This should result in Column A containing "S" above the logical_test, "X" for the logical_test, and "H" for everything below. The VBA code will then hide all the "H" rows and I will have a nice looking table.
Can someone help, please?
Jim
I want to create random numbers for a specific total within a range.
I'm a newbie to Excel. So if anyone can create and attach the excel file it will be more helpful.
The upper limit,lower limit, No of random numbers & Total has to be entered manually and the excel sheet has to provide 31 random numbers.
For example:
I want to create random numbers for 40,000 within a range of 250 to 2,000
Lower limit = 250
Upper limit =2,000
Random numbers Total will be 40,000
No. of random numbers =31
Duplicate Randoms: Allowed
1864
1800
338
780
1304
1600
1281
1239
1700
878
1139
1390
1750
540
1839
1460
750
828
1925
1724
1822
1353
1769
1950
1952
1603
1423
1999

Total =40,000

Thanks a lot.
I have an order form created in excel 2003 and need to prevent the sales reps from going over the limit. Is there a formula to add to my total line which is currently =SUM(I6:I57)
The limit is 500 dollars. thank you
I need to be able to seperate a percent from a city. Meaning I have column that is a drop down box, where you pick the city and it gives you the tax percent. What i need it to do is to then audomatically copy just the percent into the next column. So if in the Column of Taxable County you pick "Kansas City  7.55%" in the next Column it takes that percent and turns it into 1.0755.
I am inputting large numbers of data in vertical columns. At the bottom of the column, I would like to put a function in that shows the percent complete of that column.
Basically, if one of the fields have data in it, it counts towards the overall percentage complete.
So if 250 of 500 data fields have data, the percent on the bottom should read 50%.
Also, I have highlighted horizontal rows that SHOULD NOT count towards the overall value.
So, how do I do something like that, if there are say 510 total rows, with 10 highlighted rows to be excluded, and 250 rows with data in them? How do I get a total percent complete so it would read 50% complete?
I need to have 3 columns in my excel: Amount, Percent and Divisor
 Amount and Percent need to be editable.
 Amount should calculate as Percent*Divisor/100
 Percent should calculate as Amount/Divisor*100
 Whenever Amount is updated, Percent should update and viceversa.
Example:
Amount = 100, Percent = 1, Divisor= 1000 (100 is 1% of 1000)
Update Amount to 200, should result in:
Amount = 200, Percent = 2, Divisor= 1000 (200 is 2% of 1000)
Update Percent to 3, should result in:
Amount = 300, Percent = 3, Divisor= 1000 (300 is 3% of 1000)
Possible? I think that i can't have a formula column as editable? Any geniusiscious workarounds?
Hello Board,
I need a formula that would do this: Say I have 6 machine units and I have a percentage of cost for each machine. The total cost all six is 2,108,042.37... unit 1's cost is 17.03% if total , and unit 2 is 15.78% of total cost and so on. I want to keep that percent per unit even when my total cost increases. So, I want the unit percent to reflect the same percentage no matter the total cost , therefore the unit cost would have to divide the total cost into the right amounts to reflect or keep the current percentage. I pray that made so sense. My current formula is =IF(ISERROR(C18/F18),"",C18/F18) where F18 is the Total Cost and C18 is the unit cost.

