I am trying to decipher how to calculate commissions for my sales reps. I have made just a simple spreadsheet to give you an idea of what I am doing. I have tried to us an IF formula but I think there are too many options( I have 9 reps). Basically I pay them either 10 or 15% so I need a formula to take the sales price - cost times their apporpriate %.
if sales target is say 1000 if they dont reach 60% no commission payable if the reach 60% and over to sales target they get 3% if they get over target they get 5% of everything over target
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............
I need to write a formula that will look at a number in a cell and compute an amount at 1% up to 55,000. Then anything above the 55,000 compute an amount at 3.25%. Then add the two together.
So a baseline of 55,000 at 1%. Then above the baseline 3.25%.
I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.
Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.
I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.
I have a group of users in cell C1 and i wanted to count how many times they have process a payment as long as its value in Cell D1 is more then or equal to 1.
I tried sumif, but its totalling the amount. but i wanted is the number of transaction.
I need to compare in and out of money in multiple bank accounts.
Imagine in row one i have all the "INs" and in row two i have all the "OUTs"
Now, how do i compare say first transaction in row one to say 5 transactions in row 2 and find the relationship It can be:
1. Transaction IN 1 = Transaction OUT 3 2. Transaction IN 1 = Transaction OUT 2+4 3. Transaction IN 1 = Transaction OUT 3, or Transaction OUT 2+4
So if its a direct relation it just displays where they are equal, if they aren't how it will display which multiple transactions will be equal, and if there are 2 different possibilities it will show both answers.
If its only In = Out its pretty straight forward, but how do i code it to search for combinations of transactions say 1+2+5 efficiently.
1.1 In columns N and O, color the numbers in both the N and O cells green if, and only if, (a)the N cell's number is greater than O's, AND (b) both N and O cells' values are greater than the preceeding N and O cells' (i.e. a great value than one row higher in the column).
1.2 In columns N and O, color the numbers in both the N and O cells red if, and only if, (a)the N cell's number is less than O's, AND (b) both N and O cells' values are lesser than the preceeding N and O cells'(i.e. lesser values than one row higher in the column).
I have approximately 40 seperate sheets in one workbook. Each sheet is a unique part #. Each part has 6 different types of transactions possible. Let's say A-F. A-F each have a date associated when them of when the transaction occured. The transactions are sorted by date. I would like to write a formula that when Transaction A occurs what is the diffence in days until D transaction occurs. Or the time differnce between when B occured and the next F occured.
below is my datedif formula, but it obviously only works in a sequential order from top to bottom.
=IF(DATEDIF(M5,M6,"y")=0,"",DATEDIF(M52,M6,"y")&" years ")&IF(DATEDIF(M5,M6,"ym")=0,"",DATEDIF(M5,M6,"ym")&" months ")&DATEDIF(M5,M6,"md")&" days"
I want to SUM monetary transactions based on two criteria.
1.) if the transaction occurred within a certain month (Jan) 2.) based on the transactions category ("Obligations")
I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.
I have a set of sales data which shows the dates of transactions and also the product type that was sold. I want to see the monthly sales for each product type. I can get a total for all product types over the months using the following:
I have downloaded some of my bank statements in excel format but they are just static data - ie, they are just numbers in boxes and the BALANCE column does not react when I take out a transaction.
I have put in a formula for the BALANCE column so it does now take its value from the previous day plus or minus transactions, but now I want to do additional things.
- How would I, for example, categorise several transactions as "HOLIDAY" [URL] ....... and then temporarily make them disappear so that I can see the effect of that on my balance? I can see how to hide/unhide transactions but that doesn't actually seem to have any impact on the balance column.
- Second query: how do I make my current spreadsheet a template so that when I download the next bunch of bank statements I can just apply all the formulae in this one to it?
I'm trying to group a year's worth of bank transactions. The initial data that was cut from pdf files is a date, payee and amount
1) how can I search down col A and give the sum of all like Payees, then total each set of similar payees? Maybe if first 6 characters match, then total until it comes to a different set. Total each set.
2) then, I need to assign a category to each set of payees, so if contains usps, then add category "postage"
3) formula to find all postage totals and combine for a grand total per category.
We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.
I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).
How to form sub-totals quarterly in a typical list of transactions. Twist being it is not calendar year quarters but quarters from from 5 April to 5 July, 6July to 5 October etc. Typical table columns looks like:
Date Amount Transcation 5/4/14 200.50 bolts 7/5/14 50.10 screws 6/6/14 10.00 bolts and nuts -------------------------------------------- SubTotal 260.60
12/7/14 10.00 bolts and nuts 10/8/14 40.00 rivets 4/10/15 10.00 screws -------------------------------------------- Sub Total 60.00
I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:
I have these formulas auto-filled to the bottom of the sheet of each column. The problem I'm having is that with this setup, the return on the G column is giving me
#VALUE!
for all rows that do not have any values entered yet. Is there any way to fix the formula in column G so that it reads the value of the cell instead of the formula in the targeted cell?
I have a "surplus inventory" sheet and a "inventory shipped" sheet. How can I input how much I shipped in the "inventory shipped" sheet and make it take away from my "surplus inventory" sheet automatiaclly?
I am trying to recall info from the rows in another part of a worksheet when I type the first column. For example, I have over 300 rows of:
Apples 155 12oz red Orange 120 10oz orange Banana 50 11oz yellow etc.
I want to select or type only the word:
Apples
And have the rest of the columns fill in with the rest of the info, like so:
Apples 155 12oz red
A filter would work well to select, except I need about 7 in the worksheet as it is a form and excel only allows one. I can’t seem to make the advanced filter work. An IF function would work, except I'd have to put all the words to match in " " marks, which would be very time consuming.
how I can recall the info from my list to the 7 lines I need to copy them into?
how to use vba in excell. First question. 1) I have a spreadsheet with names in column one and other info in columns B-I. I would like to make excell do the following. If I enter the name a second time and so on, then can excel take the info out of certain cells from the first entry? Say column C, D, and G and auto enter them for this new row. 2) can I copy parts of a sheet to a new sheet and then when I make changes or new entries then they will update the copied sheets info?
i have this code that gives me 1st row of data , from Row 10 of every sheet in workbook, how can i modify to give me row 11 ,12,13,14,15,16 as well upto row 21 if there is Data in Col C ( max range is C10:C21) ,
For intX = 1 To Sheets.Count For intY = LBound(arySkipSheets) To UBound(arySkipSheets) If Sheets(intX).Name = "MachCapRpt" Or Sheets(intX).Name = "MachAdSht" Or Sheets(intX).Name = "Times" Or Sheets(intX).Name = "MachSchd" Then Else .Range("A" & intNextRow).Value = Sheets(intX).Name .Range("B" & intNextRow).Value = Sheets(intX).Range("B10") .Range("C" & intNextRow).Value = Sheets(intX).Range("C10") .Range("D" & intNextRow).Value = Sheets(intX).Range("E10") .Range("E" & intNextRow).Value = Sheets(intX).Range("H10") .Range("F" & intNextRow).Value = Sheets(intX).Range("M10") .Range("G" & intNextRow).Value = Sheets(intX).Range("W10")..............
