I have amount, Activation date & Validity in days. I want to segregate the amount on days wise.
E.g. acgtivation date is 1 mar, validity is 45 days and amount is 900 then it will show 2 in each day from 1st mar to 14 Apr and on 15 apr it will reflect 0.
a macro that will segreate data based on the the first string of each security description under the heading Security description and then sum the total on a separate row in the total column.
Starting row hearder in sheet "Raw_Data" start in row 6 and i would require the same in sheet "Result
Sheet "Raw_Data ...
Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.
Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the 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.
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?
I have a Userform project in Excel (naturally) and am nearly at completion. However my code runs to over 3000 lines. What is best practice in terms of how to structure it? At the moment it is all "behind" Userform1.
I'd like to partition my code into different modules according to type (i.e. Subs, Events, Formatting, etc). I tried this at the start when I was coding but I couldn't quite get it to work - I thought using the "Public" prefix would work? I then kind of just progressed anyway but now I am at the point where I need to structure it before going further.
I'm having trouble trying to setup a spreadsheet for work to do production scheduling. I have a temporary thing working so far but, but there is a minor hickup with certain products.
Products A, B & C can be processed normally on their own and each occurrence is identified with its unique serial number and can be traced through the schedule. The problem is that there has to be 3 occurrences of Products E & F before they can be processed in a single batch (They're smaller and its more efficient to process them together).
I'm struggling in that I can't process 1/3 of product E (Serial no. xyz123).
I would need to be able to identify 3 of these products, batch them together and follow them through the schedule.
Plus, if there was 8 of product E to be made, I can only process the first 6 which leaves the last 2 out.
I need to segregate a data from a master file with a particular column as a main.
for eg:- i have customer details with name in a column
address in b
mobile in c column,stat and other details in other column.
I want to segregate data according to state in different excel so how can i do the same.
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.
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.
Essentially I have column A that has a result that is a sum of time, I'm looking to assign a value based upon # of minutes in violation.
So basically I want less than 0:15:00 to equal 1
0:15:00 to 0:59:59 to equal 2
1:00:00 to 2:59:59 to equal 3
and greater than 3:00:00 to equal 4.
My data looks like this:
0:00:24
0:00:56
0:45:33
0:18:36
0:36:43
0:25:15
0:17:14
0:29:21
0:49:34
0:05:28
0:04:19
0:09:08
0:29:06
0:05:39
0:02:53
1:16:18
0:14:06
0:08:01
1:01:46
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View 5 Replies View RelatedWould like to enter a formula in column B of Table 1 (on attached file) that would identify if that customer spent $90 or more (on any one purchaese) for any of their 4-5 purchases from Table 2. Expected results have been entered in Column B of Table 1.
View 3 Replies View RelatedI'm having trouble with a complex Excel formula. I'm calculating child support owed based on the current date coupled with future child support owed. My columns are as follows:
Debt / Future Amount Owed / Monthly Payment / Total Amount Paid
10,148.72 / 83312 / 508 / 1000
On the first of each new month, the Future Amount Owed should decrease by 508. Altering the Total Amount Paid should decrease the Debt column. Progressing another month without altering the Total Amount Paid column should add 508 to the Debt column while subtracting 508 from the Future Amount Owed column.
I have a spreadsheet containing 10,000 + entries.
Each Entry is Dated within Column D2:D10786 in this format - 1-Nov-08 (example).
Lets say i have a cell on another sheet Cell A1 and in this Cell i want it to Count how many Cells contain the dates from Nov-08 in my Date column..
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 have contarct for amount $5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie $1666.66/per month.
View 4 Replies View RelatedI'm attaching a copy for verification. On the invoice sheet once the place and # in class are entered it needs to pull the amount it will payout (based on the place and the # in class). No matter what I do I can't seem to get the formula to work.
View 2 Replies View RelatedI 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.
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.
I added a new cell called Track Back on the employee search sheet.
What I want to do is only show variances for the amount of days back selected in the Track Back cell.
For example if I select the last 30 days, only the last 30 days would show up below in the sheet.
I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
I need to calculate the subscription amount based on the capacity of the wind mill.
The slab rates are as follows:
1. Up to 0.6 MW - Rs.3,000
2. more than 0.6 up to 3MW-Rs. 7,000
3. 3 MW up to 98MW -Rs. 7,000 for the first three MW and Rs.1,500 per MW in excess of 3 MW
4. Above 98 MW -Rs. 1,50,000
I had tried IF statement but was not successful.
1 . Now i need amount to be written in sheet 1 from taking value from sheet2 based on the code. The code & description column has the same value. but it will be placed in different order
Ex:
the amount should get filled up only for the status yes & no . others it should return null.
I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.
View 2 Replies View RelatedI would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
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?
Ok, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.
I tried this but it's not working:
=IF(C3="0","",C3-C2)
I have a spreadsheet(see attached) that I am needing to find the amount spent based a a "tag" I have created. If you bring up the spreadsheet and look at the dashboard sheet, I am wanting to find the total tag amount based off what is listed in the paychesk sheet. For tests I have used this formula and have put it in B6 on the dashboard sheet.
=SUMIF('PayCheck - DEC-09-B'!$F$2:$F$1000,A20,'PayCheck - DEC-09-B'!F$2:$G$1000)
But the "bank amount" field on the paycheck sheet is a negative number the value of course coming back is not right. How do I fix this?
I 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.
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)