Forum Widgets
Latest Discussions
Stock data type does not report correct Performance metrics
Hello, I've been using the stock data type in Excel 365 and I've noticed that some of my mutual funds are reporting dramatically different Performance metrics as compared to Yahoo and others. For instance, for mutual fund VMCIX, Excel is reporting 1/3/5-yr annual returns as 16.9%, 3.6%, and 10.1%, respectively. But Yahoo is reporting for the same metrics, 32.50%, 6.61%, and 11.96%. Can anyone explain the discrepancy? Are there any solutions to get more accurate (preferably, free) data in Excel? Thanks! David
Forecast Revenue based on historic cost data
We perform approximately 10,000 service orders in a year. Each service order has detailed cost categories (labor hours, labor cost, burden, material cost, equipment cost, per diem cost, subcontractor cost). Since we utilize a percentage of completion method to recognize revenue we have to determine the accrued revenue at the end of each quarter on open and unbilled service orders. My question is if there is a formula that can analyze the historic costs vs actual final billing on completed service orders and forecast unbilled service order revenue based on the composition of the costs. As an example, if a service order has a high material cost in relation to labor cost the final billing and subsequent margin percentage, (final billing - final cost) / final billing, is smaller than a service order has only labor. Currently, we breakdown historic cost and billing on completed service orders by project manager, customer, etc. to determine a historic margin percentage on completed service orders. Then, using that historic margin we forecast estimated final billing on open service orders: current cost / (1-margin). While that has worked in the past, I would like to refine the process with a more detailed forecast method. Thank you in advance for ideas.
All the rows after last "8" in PQ
Dear Experts, Can you please help on how to get a column for all the elements after Last-8 in below Table ( not sure of we can use Table.LastN to do this, but without M-code:- So, in the New Column, I should have, ,6,15,4,..12 Using UI, Thanks in Advance, Br, AnupamReceiving Error After Unpivoting Columns in Power Query
I'm trying to alphabetize a column of email addresses, separated by a semi colon. I found if I added an index column, split by delimiter, unpivoted the columns, sorted the rows, grouped the rows, and then add a custom column combining the data, I was able to achieve this. However, coming back to it today, it no longer works and is throwing a DataFormat.Error: Invalid cell value '#NAME?' error. After messing around with adding different steps to see if I kept getting the error, I noticed that no matter what I added after the Unpivoted Columns step, I will get this error. I did some research and it seemed that it was a type error so I switched all types by using "Detect Data Type". This still did not work. I also tried replacing all the nulls from splitting by delimiter and all blank cells with "Null" (not sure if there was any but wanted to be thorough). I turned on "Column quality" to see where the error was coming from and I'm getting 100% valid on all columns My code: let Source = Excel.CurrentWorkbook(){[Name="ACR"]}[Content], #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Type] = "Assets" or [Type] = "Links")), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," ","",Replacer.ReplaceText,{"ContentOwner"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "ContentOwner", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ContentOwner.1", "ContentOwner.2", "ContentOwner.3", "ContentOwner.4", "ContentOwner.5"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SiteCollection", type text}, {"Site", type text}, {"ContentId", Int64.Type}, {"ContentOwner.1", type text}, {"ContentOwner.2", type text}, {"ContentOwner.3", type text}, {"ContentOwner.4", type text}, {"ContentOwner.5", type text}, {"Title", type text}, {"Index", Int64.Type}}), #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,"Null",Replacer.ReplaceValue,{"ContentOwner.2", "ContentOwner.3", "ContentOwner.4", "ContentOwner.5"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"SiteCollection", "Site", "ContentId", "Title", "Index"}, "Attribute", "Value"), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Unpivoted Columns",{{"Index", Order.Descending}, {"Content Owner", Order.Ascending}})) in #"Sorted Rows" Note: I tried to moving the Changed Type step to after the Unpivoted Columns step and I still receive the same error. I used Table.Buffer here because the grouping did not maintain the sort I specified on this step. Once I added Table.Buffer, it did. I removed it in trying to solve this problem and I still get the error. View when on the Unpivoted Columns step: Due to the nature of the data, I cannot share the file so if there is any clarification I can provide, please let me know.Lists Comparison (Stacked & Keeping order)
I've been trying to develop an Excel function which would compare Lists, each of single column arrays and generate a stacked (side by side) comparison aligning the matched (same) elements and keeping the original order of elements of each list intact in the resulting comparison. A Two-Lists comparison function has been developed. However, it would be great if it can be scaled up efficiently to 3 or more lists comparison. There are ideas on adopting the 2-list comparison function to greater number of lists, but the present approach is slow! and it seems that the 2-list comparison itself is not done the best way perhaps. Sharing in attached excel file, the worked-out function till now, and looking for better ideas!
SUBTRACTION IN PIVOT TABLE
Dear Member, I have created pivot table using existing data. Now there is two different type of data I need to comapre. data A vs Data B. In data A , there is two different value in data A called value 1 and Value 2. and Value 3 & Value 4 in data B. Now using pivot table I want to compare sum of Value 1 of data A vs Sum of Value 3 of data B. And same sum of Value 2 in data A vs sum of Value 4 in data b and in last where generally we get sum of VALUE 1 & 3 and Value 2 & 4 , I need to subtract this value so I can find difference. Thank you
Excel 2019 hangs when trying to print any excel file
since last week, no excel files will print. Excel just hangs. mine is MS office 2019. totally frustrated. tried repairing office online several times, reinstalled the printer several times etc. word and outlook prints without a problem. Problem is just with excel 2019!!!Retrieve data from protected spreadsheets
I like to make a spreadsheet which retrieve some data from 10 different password protected spreadsheets. With the normal methode, I need to fillout every time, the different passwords for each of these spreadsheets. The preferable way is that I open my spreadsheet and it will get data from the password protected spreadsheets. How can I make a link to retrive data with the password embedded?
