I'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 have 3 fields:
Person, FinancialYear, AmountUSD
Pete, FY12, 8000
Pete, FY13, 7000
Jeff, FY12, 5000
Jeff, FY13, 5500
Sam, FY12, 4000
Sam, FY13, 3500
How to create a pivot table that has a calculated field for the difference in AmountUSD between FY12 and FY13 (for each person).
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.
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 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?
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.
What I need to calculate is:
If Quota 1 percentage => 90%, then Quota 2 percentage = sales / quota. If Quota 1 percentage < 90% then Quota 2 = sales / quota, with the maximum # being 200%.
If Quota 2 pecentage => 90%, then quota 1 percentage = sales / quota. If Quota 2 percentage < 90% then Quota 1 = sales /quota, with the maximum # being 200%
I was able to calculate this fine with one of the scenarios above, but not sure how to make it work with both at the same time.
Quota
Sales
Percentage
5000
4500
90%
Quota #1
7500
16000
213%
Quota #2
I have a pivot table with a calculated item. The problem is that it shows data for all rows EVEN when there is no data there. So the pivot table is a lot longer that it needs to be. How do I get the pivot table to show only rows with data in it? Also, all of the fields DO NOT have checks beside "Show Items With No Data".
View 9 Replies View RelatedI am trying to write a VBA macro that will mimic what i have already done in an excel worksheet i have made. The excel worksheet uses inputs for a heat exchanger and finds the temperature as it leaves the heat exchanger. This temperature is then the new input for the second line of the worksheet and it changes quite a few of the values in the sheet. I have the excel worksheet working where you just drage the row down and it does it automaticly, but i would like to have a VBA macro that all the values can be input in with the number of cycles until the heat exchanger equalizes out.
I am able to get the macro to work when it calculates just the first cycle, all the equations work my variables are ok for just one time. What i don't know how to do is make my program realize that it has to use the calculated temperature out of the heat exchanger as my new input for the heat exchanger. I know how to do it once so it is more of a syntax or understanding of a loop or an array for the values that are changing.
I have a column in powerpivot that has Products total amounts. Some of them are negative amounts but I want them to be positive. How would I write an IF statement for it? I want it to say " If Division amount column is a negative, then multiply by -1 but it is a positive , leave it as a positive" .
View 2 Replies View RelatedTake a look at the attached spreadsheet: Value_Lookup.xlsx
We have a pivot table with the "Product" on the Row context and a date ("FullDate") selected on the filter context.
The FactTable is related to the Calendar table by "FullDate" (which is marked as Date Column in Powerpivot)
In the Calendar Table, next to "FullDate" we have 2 more dates in the same row: "1 Month Back" and "1 Quarter Back".
We just need 2 calculated fields that return the "Price" value from the selected "Product" on the Calendar dates "1 Month Back" and "1 Quarter Back".
It is a few years since I worked in the IT industry and used EXCEL a lot.
I want to use (formula) SUM on a cell which 'sums' 3 other cells one of which has a the result of a formula but the result of that SUM is always 0 (zero). I am sure I can remember doing this in the past but cannot determine how it is done.
eg.
Cell Contents
C10 1000 (value)
C11 =A1*(A10/A20) (formula)
C12 2000 (value)
C13 =sum(C10:C12)
What do I need to do so that (cell) C13 results in the correct value instead of 0 (zero)
I am entering data for a month on one worksheet. The next month I change the date and reenter new data. I want another worksheet to record the data on a month to month basis, one month in one column, the other month in another column, to have year to date. Is there anyway to do this. Currently, I use an if statment =If(a1="January",b1), but I need to change these to values before I do the next month, and alter the formula to reflect the new month name)
View 11 Replies View Relatedif its possible to place a value (the product of a formula) into a cell ie:
Cell A1 = "a"
Cell A2 = "b"
I would like to be able to say: If A1="a" and A2="b", then place "Yes" into D1.
I realize that =if(and(a1="a",a2="b"),"Yes",0)
placed in D1 would give the same result, but I do not want to have a formula in D1, I simply want the characters "Yes" placed in there.
I am trying to input data validation on a calculated field. The calculated field (sum of 10 fields above it) must not total over 100%. I have used the following in the data validation menu: Allow: whole number. Data: less than or equal to 1
This doesnt seem to be working when I input values of a total greater to 100 from its input cells however, it does return an error if I manually input a number over 100%, then it gives me an error message. Edit - sample sheet attached.
I have a list of figures (around 100 long) and I have a field which calculates a value somewhere within the range of the list (between the lowest and highest value in the list). I would like to round my calculated value UP to the closest value within the list.
For example
List : 1 3 5 7 8 9 11 13 15
Calculated value =9.4
Therefore the rounded up value I want to obtain is 11.
I have a pivot table that looks like this:
Code:
productname impressions cost CPM
product1 1,000,000 4523 4.7
product2 5,000 2400 48.45
product3 3,000,500 6,000 3
CPM is a calculated field that uses the formula =cost/(impressions/1000)
The problem is that the numbers in the CPM field are not, in practice, the results of that formula. They are averages (since it's a data field, I had to choose whether it was to be a sum, average, etc.). This means that, for example, the CPM 4.5 on the first row is not a calculation using the two numbers to its left. It is the average of the output of thousands of calculations done on the raw data, in which every line item with a productname of product1 had its CPM calculated, and then all those output CPMs were averaged together.
This is not what I want. I want a field that calculates based on what's already in the pivot table. So that the formula would be more like:
=(sum of cost)/(sum of impressions)/1000
I have a calculated field - if it is 0 I would like to now show the 0 - just a blank cell - can I do that?
View 1 Replies View RelatedI have created a macro to run different imports on the system and I need a message box calculated at the en displaying the message "Write Total Charges to Import: xxxx".
The total is the Sum of the cells listed on Column B. I have defined my total and need to show the message, and after the user has writen down the total and clicks OK, the cell showing the total is deleted. how to make it work?
The syntax I am using is:
Range("B" & EndRow + 2) = "=SUM(B2:B" & EndRow & ")"
MsgBox "Write Total Charges to Import: "
Range("B" & EndRow + 2).Select
Selection.Delete
The calculation to display is the result of (Range("B" & EndRow + 2) = "=SUM(B2:B" & EndRow & ")") but when I add the formula next to "Write Total Changes to Import" does not work.
#1
1
2
3
4
5
6
7
8
#2
Red
Yellow
Green
Blue
Violet
[Code] .....
I am trying to determine how many times a value occurs in each quartile in a data set, with the range of my quartiles changing for each row.
Above is a simplified sample of the data I am working with. What I would like to do is calculate the length of a quartile for each row, then determine whether "Red" is in the 1st, 2nd, 3rd, or 4th quartile
Right now I have two output tables. The first is a "Quartile" table, which for each row counts the number of values entered, divides by four, multiplies by the appropriate quartile and rounds down. For this I use the function
=rounddown(counta(range)/4*(quartile))
For Q1 in this dataset, that is =rounddown(counta($B2:$I2)/4*(1))
Then I have a table which calculates whether "Red" occurs in each quartile, with references to the cell values in my quartile table. My formula for Q1 here is
=countif(index(row#2,1,match(Q1 value,$row#1,0)):Index(row#2,1,match(Q1 value,$row#1,0)),"Red".
The actual formula (can be used if pasting table values into excel) is:
=countif(index($B2,$I2,1,match(Q1,$B$1:$I$1,0)):index($B2:$I2,1,match(Q1,$B$1:$I$1,0)),"Red")
Where Q1 equals my rounddown formula noted above, returning "1" in this case. For subsequent quartiles, I change my reference in the Match formula to start with the previous quartile +1, and end with the current quartile.
I need to insert 2 calculated rows in a data table for each state, one is Direct +2%, and Contractor +2%. Ideally, I would like to copy down the field name above. Every record in the table has the same 2 Categories (Direct & Contractor) and 2 calculations need to be applied. There are ~ 50,000 records so copy insert is not an option. Can a macro be applied to do this ?
Current
StateCategoryCount
New YorkDirect150
New YorkContractor75
BostonDirect200
BostonContractor125
Result
StateCategory Count
New YorkDirect 150
New YorkContractor 75
New YorkDirect +2%153
New YorkContractor +2%76.5
BostonDirect 200
BostonContractor 125
BostonDirect +2%204
BostonContractor +2%127.5
I am working on spreadsheet that calculates yearly totals. It is set up similar to this:
Jan Feb Mar Apr May ETC. TOTAL
Charge 123 123 123
Goal 223 223 223 223
Cash 111 111 111
% Coll. 50% 50% 50%
The "goal" column is Self calculated for the current month (I.E. it's April, that goal is calculated) The "Charge, Cash, and % Collections" columns won't be fill in for April until its over.
have it add Aprils "Goal" but not add "Mays" and so on... Doing this for the entire year.
Date OutTime OutDate InTime InTime Difference
01-03-099:0001-03-09NIL
02-03-09NIL02-03-09NIL
03-03-09NIL03-03-0910:002 day(s) 1 hours 00 mins
03-03-0911:1503-03-0918:000 day(s) 6 hours 45 mins
04-03-099:00NILNIL
05-03-09NILNILNIL
06-03-09NILNILNIL
07-03-09NIL07-03-0914:453 day(s) 5 hours 45 mi
In Column E in want the time difference to be calculated as shown above.
Column 2 is a calculated value (a sum of several other columns). I cannot get the code to work. When I enter the same value in the field it works, but not when it is a calulated value.
I know there must be a simple error in here somewhere...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
Select Case Target.Value
Case Is > 382: Target.Offset(0, 0).Interior.ColorIndex = 3: Target.Offset(0, 0).Font.ColorIndex = 2
Case Is > 315: Target.Offset(0, 0).Interior.ColorIndex = 30: Target.Offset(0, 0).Font.ColorIndex = 2
to copy the calculated value of a cell to another worksheet.
I tried =IF('Actual Hours'!D2="*"&"LR"&"*",'Actual Hours'!D2,"")
which when copied down would return values with LR in them, however D2 is a calculated cell (='RAS SENT 2'!L2) so it returns a blank, and I dont want to just reference this other cell instead.
Is there a way to return the value of a cell?
I am trying to blank out Column D if there are no furthercalculations required. As you can see now by the example that I have attached the formula continues the calculation.
View 2 Replies View RelatedCan I add a calculated field formulas in Excel pivot table. Such as CONCATENATE?
View 2 Replies View RelatedIhave a pivot table that takes data from a table, groups the duplicate items and adds their values up - so it shows just one entry for each item. I now want to add a calculated field in there so that I can divide one of the fields into another.
View 14 Replies View Related