For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
I am trying to write a calc for a total an amount based on a list choice
Here is the Set up
Fields A1 thru A255 are number 1 to 255
Fields B1 thru B255 are number 255 to 1
Field C1 is a List box set using A1 to A255 and its list
Field D1 is set as the needed Calculation
When you choose a number in C1 the D1 calculation should total the amount of Fields B* to B1, * being the corrisponding B field to the A field from the list
Example C1=100 (Field A100) D1=Sum of Fields B100 to B1
The MS State Tax Credit maximum per person, per year is $500.00. I need to multiply the monthly credit allowance by 12, then multiply that by 25%. This answer must not exceed $500.00. I need this formula for both the male and female. I can't figure out how to make the formula both a SUM and an IF.
EX:
(79-D22)*(D20*12*25%max $500)+(83-F22)*(F20*12*25% max $500)
I want to use a combo-box on my sheet to deduct 30% from an amount in cell A1 if 'Yes' is selected in the combobox. For arguments sake the amount in cell A1 is 100.
I want the new total to appear in cell A2
If no is selected in the combo-box, then no calculation is performed and 100 remains in cell A2
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001
On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?
Hi all,
I have a workbook with 2 sheets: "dan" and "resources"
On the "Dan" sheet, in column B there is a list of names and in column column C, a corresponding dollar amount.
On the "resource sheet" in column B is a subset of the names found on in column B of the Dan sheet. On the Resourse sheet, there is a corresponding dollar amount in column c also.
I would like a macro to compare the dollar amounts to ensure they match. If they match, I would like to place an "OK" in column Q on the appropriate row of the Dan sheet. If the amounts do not match, place an "X" in column Q of the appropriate row on the dan sheet. If the name on the Dan sheet is not one that appears on the Resource sheet, then place an "N/A" in column Q of the appropriate row on the Dan sheet.
I want to create a macro that will copy and paste a couple seperate collumns but the problem is that each month, and each invoice, contain a different amount of rows so I can't "record" a macro. Lets say I start on A4 which is the heading of Column D..below it are a bunch of records at the end of the records is a space. I need it to stop there. Then do the same for Column G, I, etc. I would like it to copy each column and paste in a new workbook.
View 9 Replies View RelatedI need some code to do the following.
Look at worksheet 1. Find column "Amount". Insert column next to amount.
For all highlighted cells in column "amount" insert text "R" in adjacent (inserted) column.
I have a table that looks like:
7300
3
7301
2
7302
5
And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
View 9 Replies View RelatedI have an excel sheet and I want a Msg pop up to show a certain amount from a specific cell. So if the cell shows $100. I want to a do a macro that if I hold control + shift + a I will get a pop up saying "Your amount is (value of the cell).
I can only get it to work if I want the vale from an active cell.
MsgBox "The value in the active cell is " & ActiveCell.Value & "."
I am using the close.workbook command in VBA to close a workbook I opened to copy data from. I am getting a message that says "You have a large amount of data on the clipboard. Do you want it available?" What command do I use in VBA say no to this message?
View 5 Replies View RelatedPhasing Design.xlsx
We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).
We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.
So far i have the following code:
VB:
Sub phasing()
Dim SourceA As Range
Dim Weight_ResA As Range
Dim TargA As Range
Dim i As Long
[Code] .....
I have been using this macro to separate lots of rows with a certain amount of blank rows.
Example:
Code:
Sub test()
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
[Code] .......
I trying to change it so that, instead of inserting how ever many blank rows, it just copys the row above.
Example (Row A to C)
I have been messing around with macro recorder and i could select each row and paste it into the blank rows, but from researching on the internet selecting and pasting data seems to be a waste of resources (ram?) and i will be running this on a couple hundred rows (lots of data). Also, i would like to keep the functionality so that i can still choose how many times it copys/inserts each individual row from a input box.
I eventually want the data to end up like it is shown in the table but i am takin it one step at time because i want to understand what the code is doing.
How can i sum up the amount of all "case" sold in the second month of 2009.. so the output should appear in C11 as i have already done manually.
View 9 Replies View RelatedTrying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march.
The format for the date is 3-12-09.
Here is what I'm trying.
=SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)
I have a total amount of $52,000 and I want to show an equal amount in twelve
columns which represent 12 months from sept 2005-sept 2006. My calendar goes
for 3 years, jan 2005- jan 2008. How do I get this amount to show up in the
correct columns.
I want to sum income of my team by name without sort.
Example: I have as beloow in excel:
$ 10.91 Khear
$ 10.91 Khear
$ 10.91 Vichda $ 22.73 Linda $ 31.82 Linda $ 13.64 Vichda $ 10.91 Khear
$ 10.91 Khear
$ 10.91 Linda
$ 13.64 Virak $ 10.91 Khear
So I want to calculate name Linda, how much? Khear How much? without sort or separate for calculate.
How can I display the $xx.xx into a statement?
I have these $ amounts in column A:
99.12
107.65
14500.99
Have the result in column B as:
Please Pay Exactly$00000xx.yy
(where xx is tenth, hundreds, thousands and yy is the cent)
00000 is to prevent forgery
and to have the results in column C using same column A:
Please Pay Exactly$0000xxDollars and yycents
For example like Please Pay Exactly$000099Dollars and 12cents
or Please Pay Exactly$000014500Dollars and 99cents
I need a formula that will replace a dollar amount with an X. In other words...I have 10,000 cels and half of them have dollar amounts in them...I would like to replace them with the letter X without having to manually do it...
View 9 Replies View RelatedI've written a custom function that calculates commissions (as a %) based on three variables.
I wanted to know what code I can use to cap the amount of commission paid out to a specific % amount. Something like "as soon as X exceeds 3 times the amount of Y, payout 3 times Y as the maximum".
I need a formulas to add the row & column amount , see the attached example sheet.
Row and Colums.xlsx‎
get sorting out top 3 clients with the total amount.
A
B
C
1
Company Name
Value
2
Apple
2100
3
Mango
4000
I found that =LARGE only work for single cell name with single value instead of multiple cell that may contain same name but different value and add them up.
is there a way to create a formula that caps at a certain amount?
i am trying to create a spreadsheet for a group of doctors and their insurance coverage. i want to display that a certain amount of coverage caps at a certain price over the course of a number of years.
so basically, can i create a formula that automatically adds the data and max's out at a certain number?
I need to create a price list a we give a discount based on the price of the product for example:....
All products over £100 have a 10% Discount, how do this and also at the sametime round the value up to 0.99p. ie Product ZXY123 is £123.99 10% off is £111.59 we need the calculated price to be £111.99.
a1: "house $350.000 11/2/08" or "house 350.000 11/2/08"
should retun in b2 -> 350.000
anyone here?
I am using excel for invoice making...In the end i have a total amount. I need to type it in words for every invoice at present.
How can i convert it to words automatically? Is there any formula to it?
Or is there anything else? Please suggest me friends.