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".
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
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.
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 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" .
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.
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)
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.
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:
I 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 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.
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
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.
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.
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.
Ihave 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.
I need to see easily if my cell is giving a value by using a SUM. This is so I can see if the SUM has been taken off or overridden by typing in a number.
Year 1 Year 2 Income 100 150 Expense 20 30 Net Income 80 120
I need to take the calculated "Net Income values" and also place them vertically by themselves, like this:
Year 1 80 Year 2 120
I know I can copy and paste the values easily using the "paste special... transpose", but how can I easily do this so that it continues to reference the original values? So that if I change the Income in year 1 of 100 to 120 the vertical Year 1 value would change from 80 to 100?