I have weekly budget report (for the entire year) that I put in a Pivot Table with approx 20 different row labels. The actual revenue numbers come only once a month (several different reports make up the actual revenue numbers). I need to put the actual numbers on the same row as the budget numbers and I can't figure it out.
For example, for the first three months of the year, I have the pivot table showing everything perfectly. In February, I get January's actual revenue numbers that now "replace" the budget numbers for January. If this was a regular table, I could easily overwrite these numbers, but with the pivot table, I can't get these two fields to line up. For now, I have the Actual Revenue numbers on the Rows above the Budget numbers for their respective months/quarters.
In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.
I was wonder if there is anyway in excel to do the following. I have a table that shows %'s,,, and I have formatted it to show 1 decimal place. Is there anyway to do it so that if the % is a whole number, IE 100% it shows just 100% and not 100.0%
So; basically, all %'s that aren't whole numbers will show 1 decimal place,, whole numbers to show NO decimal place.
I can see in excel a custom formatting under the Number tab,, but if this is where you do /can do it,, I'm not sure how.
When calculating percentage difference do I always subtract the smaller number/cell from the larger number/cell, then divide by the smaller number/cell or vice versa? Maybe I am over thinking it, but it is confusing the heck out of me
I provided an example below to illustrate a spreadsheet I work on. The numbers go up and down. I want to know how I should go about setting up the formula. If I need to ensure the smaller number is always subtracted from the bigger number, it will require a lot of manual intervention on my part.
Row 3 is =(b2-b1)/ABS(b1)Row 4 is =(b2-b1)/ABS(b2)Row 5 is custom. I went through each cell and made sure the smaller number was being subtracted from the larger number, then divided by the smaller number
I have a spreadsheet which has a number of columns populated with a day number and below each day number there are some numbers. What I would like to do is to compare the values for Day1 with Day2, Day 2 with Day 3 etc. Is there a smart way to do this using VBA? I am enclosing an example to show what I would like the macro to do.
I have an excel file that linked to an external feed that receives stock prices in real time. on the file i have the stock name, the price paid and the current price. I would like to have a popup that shows up whenever a stock price has a 15% or higher return. I would like the popup to show the stock name. I realise that conditional formatting would highlight whatever is over 15%, but the workbook has multiple worksheets and I would like it to popup even when i am on another worksheet.
Enclosed is a copy of the file (the actual file has many more sheets) : john.zip
Using conditional formatting i am trying to write a formula that changes colour on the percentage difference of another cell.
An example would be if cell A1 contained a target of 5% in cell A2 would be an actual. If cell A2 is equal to A1 or better then it would be green. If cell A2 was within 10% less of A1 (i.e 4.5%-4.99%) then amber and red for the target minus 10% (i.e <4.49%)
I want to do this as a conditional formatting formula as the target figure will change but 10% difference will remain the same.
I have an excel file that linked to an external feed that receives stock prices in real time. on the file i have the stock name, the price paid and the current price. I would like to have a popup that shows up whenever a stock price has a 15% or higher return. I would like the popup to show the stock name. I realise that conditional formatting would highlight whatever is over 15%, but the workbook has multiple worksheets and I would like it to popup even when i am on another worksheet. enclosed is a copy of the file (the actual file has many more sheets)
Excel 2010 - Solver will NOT work with whole numbers and that is what I need (only whole numbers, i.e whole vials being used). So this is a drug/inventory problem. The drug is dosed as 25, 35,or 50 units/kg. Also a Max dose is reached with 100 kg. (this I can do). The trick is we want to use whole vial sizes and the vials come with varying amount of drug per vial (480-620 units/vial). We can enter our existing Inventory to get the exact units/vial and number of vials on hand. Now I need to figure out the best combination of vials in Inventory to use to get the closest to the dose needed. If Max dose is reached then the MOST we can go over the max dose is 5%. I'd like to see the percentage difference from the calculated or Max dose.
I have a Pivot Table with fields for months and weeks. I also have a "Show Values as % Difference Field" that shows monthly or weekly % change. When I collapse the fields so that it goes from weekly to monthly (or vice versa), I have to manually change each Show Values As % Difference column. Is there a way to do this automatically or quickly?
I'm trying to automate the attached schedule so that the formulas in H stop increasing once the amount in column J equals zero. So far everything I've tried either gives me a circular reference error or ends up giving me the same result as if I depreciated the asset an additional month.
I need to calculate the amount of time between two dates, and show it in the number of hours and minutes. My dates are formatted to show the time in Zulu time. So 1406 = 2:06PM.
Here is what I have:
A1 = 6/4/08 1406Z B1 = 6/5/08 0402Z
For this example, the formula should return a result of 13 hours and 58 minutes. With the result, there is no need to desingate Zulu time with a 'Z'. But I need the result in a format that will allow me to calculate an average for all my data.
I'm looking for a macro or formula that can calculate the difference to the lowest of col C based on option in colB. See attached. I complete what it should look like in F. Basically, this can be done by sorting the data by colB and using this formual for every group. =C2-MIN($C$2:$C$4). This is not ideal because I have many of these to do and you have to adjust the min range for every group. Plus group sizes will vary.
There has to be a better way. Ideally, I would not like to have to sort the data, unless a macro did it for me....
I am trying to figure out what formula to use to calculate the difference between the creation date and resolved date in hours and days. That is, In once column I would like to see the difference in 'hours' and in another column I would like to see the difference in 'days'.
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file.
The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
how to calculate the difference in value of an item between two dates on a pivot table. I have one table that feeds the Pivot Table and it has three fields:
Data as of Date Item Value
So each day, the Data as of Date is updated to the current day and the items are the same but the values are different. I need to calculate the difference in value between the most recent date and x days ago. I cannot figure out how to do this via Pivot Table.