Edit A Calculated Field From A Pivot Table |
Edit A Calculated Field From A Pivot Table - Excel |
|
I have a pivot table which I have created a calculated field called WMAPE which stands for Weighted Mean Absolute Precent Error. The formula looks like this.
=IF(AND(SUM(Actual)=0,SUM(Forecast)=0),0,IF( SUM(Actual)=0,100,'Abs Error'/Actual))
The first IF is checking to see if a forecast and actual existed, if both values = 0, then WMAPE = 0 since there is no error.
The second if is going to check to make sure if Actual equal 0 than WMAPE is 100 because the user forecasted a value, but nothing shipped.
Else, if neither condition exist, WMAPE is Abs Error / Actual.
It turns out that some of my raw data for Actual contains negative numbers which is causing my WMAPE to return a negative number which is not correct.
I would like to edit the calculated firled to add another IF statement to fix my negative problem. However, I cannot find out how to do that?
Any suggestions?
Similar Topics
I am trying to calculate our On Time Delivery. I want this as a simple percentage of jobs. I have got this running in the following way:
Column M - Estimated Delivery dates
Column N - Actual Delivery dates
Column O - =IF(SUM(M2-N2)>0,1,0)
Then I have calculated On Time Delivery as: =SUM((SUM(O2:O252))/(COUNTA(N2:N252)))
This seems to work fine. My problem is, if we enter a date in Estimated without a corresponding Actual date, the formula for Column O fills out anyway and improves our On Time Delivery Percentage. How can I set this up so that the formula doesn't calculate if there is no data in Column N?
Any help much appreciated
Thanks,
James
Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.
Please HELP!!!
would really appreciate anyones help with this.
I have a column full of text-formatted fractions...
4/5
6/4
3/1
2/5
4/5
etc, etc.....the column is very long!!
I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.
If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!
Any suggestions on how I could speed this up?
thanks
Here is the example.
Jan2011 Feb2011 Mar2011 Apr2011 May2011 Jun2011 Jul2011 Aug2011 Sep2011 Oct2011 Nov2011 Dec2011 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- Product XXXXX Actual/Forecast : 174 1674 574 574 524 524 524 524 524 728 728 728 New Purchases : 0 0 0 0 1002 0 0 0 0 0 0 0 Goods In Transit: 0 1506 0 0 0 0 0 0 0 0 0 0 Other Movements : 0 0 0 0 0 0 0 0 0 0 0 0 Closing Stock : 1270 1102 528 -46 432 -92 -616 -1140 -1664 -2392 -3120 -3848 Week Stock in Hand ? ? ? ? ? ? ? ? ? ? ? ?
So, in Month of Jan2011 the Closing stock is 1270, need a formula to calculate how many weeks will this stock cover look at future forecast.
How can I do this? I only seem to have the options for sums, counts, etc. There are no options for just presenting the data.
So for example, rather than a PivotTable that results in counts of 1, 1, 1 in each cell, I want it to show Compay1, Company2, Company3, etc.
I am running Excel 2011 for Mac.
I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.
Appreciate any help, let me know if you have any further questions.
Hunter
E.g.
Manchester United 3 - 0 Arsenal
Prediction: 4 - 1
Points: 1
I've found a formula online that works for most scores; the correct score (e.g. 3 - 0), a correct win (e.g. 4 - 1), and in the case of a draw (e.g Man Utd 1 - 1 Arsenal, and the prediction 2 -2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect sco 1
The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0)))))
Could someone help me work out how I can write a formula to add before the above that will check the contents of four cells (the home score, away score, home prediction, away prediction), and if the number in all 4 cells are the same, allocate 4 points? I've tried using averages, but some combinations would void this. I've also used the SUM function, but the same happens, as with MULTIPLYING.
Something like =IF(A3:D3) are equal, 4,0
Below are a few examples of what I mean:
(Result) (Prediction) (Points)
A B C D E
3 - 3 3 - 3 4
2 - 1 3 - 1 1
1 - 0 1 - 0 3
3 - 3 2 - 2 1
Apologies in advance if I haven't explained this clearly enough.
Thanks
Will
It looks like this:
=(E3/((A3+D3)/2))*12/months
So if I substitute with some actual data:
=(6/((22+33)/2))*12/months the answer becomes 21.8% (as the annualized voluntary turnover rate).
What does the *12/months actually do mathematically? I cannot find it in the excel help files and have not been able to figure this out using math (on the calculator).
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the x-intercept. Any help would be greatly appreciated.
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
If that number is less than -100, I want it to show as blank.
Any help?
Right now, I'm trying to accomplish this by making a conditional format...meaning when my cell equals less than -100 I make the cell color and font the same (so it looks blank, but its not). When I print it, it still shows the negative value.
Thanks.
Hoping someone can help me quickly as I'm pulling my hair out and have a deadline getting too close.
Been trailing the net and this board but can't work out how to do a simple variance.
How do I work out the difference between two numbers - including negatives.
If the numbers are all positive, it's fine as it's simply a case of A - B = C which is your difference. However, that doesn't work if B is negative.
I need a formula that would give the following:
First Number / Second Number / Difference
1 / 5 / 4
1 / -5 / -6
-1 / 5 / 6
-1 / -5 / -4
-5 / -1 / 4
Hopefully there's a simple function I've been missing.
Thanks in advance, R
So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually "hit" the submit button on the webpage.
For posting, I removed my actual user name and password and and used the generic " User Name " and " Password " highlighted in blue.
I highlighted another section in green that I took from a previous post hoping it would solve my problem. The link is: http://www.mrexcel.com/forum/showthr...xplorer+submit
Here is the code I am using:
Sub GoToWebSiteAndPlayAroundNew()
Dim appIE As Object ' InternetExplorer.Application
Dim URL As String
Set appIE = CreateObject("InternetExplorer.Application")
URL = " https://efolio.morgankeegan.com/escripts/defaultLogon.asp?errCode=2 "
With appIE
.navigate URL
.Visible = True
Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop
.document.getelementbyid("fUserName").Value = " UserName "
.document.getelementbyid("fPassword").Value = " Password "
End With
On Error Resume Next
x = 0
For Each mitem In IE.document.all
mitem.Value = "x"
x = x + 1
Next
x = 0
For Each mitem In IE.document.all
If x = "Submit" Then
mitem.Click
Exit For
End If
Next
End Sub
I am using
ActiveSheet.ShowAllData
in a piece of VBA and it causes an error if the sheet does not have a filter applied! Is there an if statement I can use to check if the data is filtered?
Thanks - this site is an absolute life saver!
Rich
Cell B5: start time: 4:15 formatted as 4:15:00 am
Cell B6: finish time: 6:15 formatted as 6:15:00 am
Cell B7: total time: 2:00 formula in cell: =TEXT(B6-B5,"h:mm")
Cell B8: total batches processed: 22 (this is entered manually)
Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,"h")
as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour.
If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem?
answer should be 11 per hour.
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
Can VLOOKUP be used on pivot tables? If so, is there any special syntax I need to use?
Thanks