Create An If-then Formula In Excel To Limit Column Total?

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 auto-populate 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 vice-versa.

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.