Last 50 Posts on Chandoo.org

Lilian Medina-Taboada on 2019-06-24 19:12:55
Hi Chandoo, RE: Matching transations using formulas in Excel I do have a problem using the formula. I need to mach by Cost center by amounts received and paid to a total of zero, using two extra criterias: Revenue Category and Spend Category Could you assist on this one? Cost Center Journal Source Accounting Date Revenue Category Spend Category as Worktag Transaction Amount Matched? Sum CC60300 Contributions Balance Forward 7/1/2018 Investment Clearing - R0015 (1,087.68) =ROUND(SUMIFS([Transaction Amount],[Cost Center],[@[Cost Center]]),2)=0 0 CC60300 Contributions Balance Forward 7/1/2018 Investment Clearing - R0015 1,087.68 FALSE 0 Thanks for the help. Lilian

Arvind on 2019-06-24 18:07:34
Thanks for the tips and tricks on Excel. In the Planned versus Actual chart examples, you use multiple values (ex. multiple Categories in above). How can this be done when we have only 1 set of values? For example if I have only this: Planned Actual SOW Budget 417480 367551 How can I create a single bar chart like the one above?

Arun on 2019-06-24 10:13:51
It worked & thanks a lot

Jeff on 2019-06-21 21:10:08
If have a list with product name, dates of sale and price on that date of sale Your tip allows me to find first and last date of sale But I need price on the first date of sale and price on the last date of sale... How do I display those values in my pivot?

Lalith on 2019-06-21 11:02:32
Dear Chandoo, Can you pls explain the inclusion of countifs formula in sumproduct. I just can't understand if it makes a 5x1166 array or a 1166x5 array. Thanks in Advance!

Unmesh Deshpande on 2019-06-21 08:17:18
Hi Chandoo, Amazing work for the community. I really appreciate the same. The download link to the entire dashboard doesn't work for me. Below is the link that am referring to: http://cid-b663e096d6c08c74.skydrive.live.com/self.aspx/Public/dynamic-dashboard/Dynamic%20Dashboard%20Illustration%20V1.1.xlsm

faye on 2019-06-20 14:31:41
Help! How do you do the same trick in Excel for Mac? This functionality has been so helpful in Excel for Windows, but I'm unable to find the translation to Mac's Excel. Very frustrating. Really hoping you can help uncover this translation mystery!

GMF on 2019-06-19 16:29:14
1) Learn Power Pivot, Power Query, DAX, M regardless of the other issues. 2) If your organization doesn't support PowerBI, don't bother learning it until they do. Graphics sitting on your desktop don't help if you can't easily publish. Maps and charts are nice if you need to send images but it's a nice-to-have if you can fit the learning into the rest of your work, not a must-have. 3) If your expected graphics and dashboards are stable, learn PowerBI. If you're still figuring out what numbers are important, or people are just asking for one or two numbers, stick to Excel for now.

Chihiro on 2019-06-19 12:57:15
I'd recommend anyone dealing with numbers to learn PowerBI. Though in practice will depend on roll. If your role involves financial modeling, reporting, audit, then it's of benefit. With more and more executives using mobile device to access reports, it's beneficial to publish reports in web service, rather than in Excel file. Though I'd recommend starting from learning data structure and modeling, then data transformation, before learning DAX. DAX is highly contextual and can become unnecessarily complex without proper underlying structure. As for Monte-Carlo simulation, or other type of statistical analysis. PowerBI integrates both Python and R for that purpose.

Ashutosh Singh on 2019-06-19 07:01:33
I really appreciate the effort

Charles on 2019-06-18 20:57:48
Agree with Angie. The report is great with the filters by various demographic elements, but the core calculation needs to be changed. You are presenting a "net hire" figure, which has value. But for turnover, for what ever period the filters use, you calculate average headcount and the sum of "leavers." E.G.: January starting 100 + 10 hires - 5 leavers; 5/110 = 4.5% turnover February staring 105 + 10 hires - 5 leavers; 5/115 = 4.3% March starting 110 + 10 hires - 5 leavers; 5/120 = 4.2% Q1 ((110+115+120)/3) = 115 avg; 15 leavers/115 avg active = 13.0% turnover So how do you do a dynamic calculation that averages the monthly active headcount and sums the leavers for any selected period (month, quarter, YTD, YE)? Some people may vary the calculation method for active vs. what I show, but this should be good for illustration. Thanks!

Mike on 2019-06-18 12:06:17
Do anyone know how to save as UTF-8 format? Problem is that I have scandinavian letters and ANSI do not support those. --------- Sub savetoText1() Dim FileToSave As String, lineText As String Dim myrng As Range, i, j FileToSave = ThisWorkbook.Path & "\test" & Format(Now, "yyyymmdd-hhmmss") & ".xml" Open FileToSave For Output As #1 Set myrng = Range("xml!$A:A") For i = 1 To myrng.Rows.Count For j = 1 To myrng.Columns.Count If lineText = IIf(j = 1, "", lineText & " ") & myrng.Cells(i, j) Then Close #1 Exit Sub End If lineText = IIf(j = 1, "", lineText & " ") & myrng.Cells(i, j) Next j Print #1, lineText Next i Close #1 End Sub

Mike on 2019-06-18 06:11:39
Nice vba, just asking how to filter empty rows away from print?

byundt on 2019-06-17 21:07:07
The attached discussion is my attempt to improve Chip Pearson’s formula. The suggested formulas don't require that the beginning and ending times bracket the work shift. The formulas use four named ranges: beginning and ending (of the period being measured), which include both date and time elements ShiftBegin and ShiftEnd which include only the times for the start and end of the work shift The formulas are only valid when starting and ending dates are different! By way of explanation: 1. Time consumed on the first and last days of the interval is figured using the MOD expressions. 2. Time consumed in the interior of the interval is figured using (NETWORKDAYS -2)*Shift length. Since NETWORKDAYS excludes weekends, you may include those weekend days by using (INT(ending) – INT(beginning) -1) instead of (NETWORKDAYS-2). 3. If the work shift spans midnight, its duration may be calculated directly using MOD. If not, the overall elapsed time (excluding intervening days) less MOD(rest end – rest start,1) is used instead. MOD(ending,1) returns just the time portion of the date/time serial number MOD(ShiftEnd – ShiftBegin,1) returns the number of hours in the shift, regardless whether that shift spans midnight or not. This expression assumes the number of hours in the interval is less than 24. Work time =IF(ShiftEnd>ShiftBegin, (MOD(ending,1)+1-MOD(beginning,1)) - MOD(MIN(MOD(ending,1),ShiftBegin)-MAX(MOD(beginning,1),ShiftEnd),1), MOD(MIN(MOD(ending,1),ShiftEnd)-MAX(MOD(beginning,1),ShiftBegin),1)) + MAX(0,NETWORKDAYS(beginning,ending)-2)*MOD(ShiftEnd-ShiftBegin,1) Rest time =IF(ShiftEnd>ShiftBegin, MOD(MIN(MOD(ending,1),ShiftBegin)-MAX(MOD(beginning,1),ShiftEnd),1), (MOD(ending,1)+1-MOD(beginning,1)) - MOD(MIN(MOD(ending,1),ShiftEnd)-MAX(MOD(beginning,1),ShiftBegin),1)) + MAX(0,NETWORKDAYS(beginning,ending)-2)*MOD(ShiftBegin-ShiftEnd,1)

christie on 2019-06-16 15:46:00
Hello Could someone tell me how to solve Q2 in below workbook https://chandoo.org/wp/calculate-vacation-days/

Paul on 2019-06-14 19:53:35
I think you need to learn both. Think of it this way, one is a shovel, one is a rake. Two different tools but both critical to make the garden work. An added benefit is that Power BI will allow you to take your Excel financial reports (or a part of them) and add them to a dashboard. So you get the best of both worlds for your overall reporting. Each tool doing its specific task really well.

Gilson on 2019-06-13 11:35:50
Yes, the URL is ok when testing in browser. Only the result showed in the ceel of Excel is #VALUE

Chandoo on 2019-06-13 05:47:36
@Mohammed... Interesting question. But I am not sure I understand all of it. Can you tell me what should be the sample answers?

Chandoo on 2019-06-13 05:45:47
@Gilson, can you test the webservice call by pasting the URL in browser? Does it give a result? If not, you may need to find the correct URL + parameters.

Chandoo on 2019-06-13 05:45:01
Hi Crhis, I like how you have hulk (superhero) as your avatar. Do you know that there is a superhero in Excel too? It's Power Query. You can use it to solve your problem in a simple click. Here an intro if you need some guidance. Powerful Introduction to Power Query

Chris on 2019-06-12 16:06:20
Is there a way to update the source once you have added to the data model? I receive a new spreadsheet weekly and would like to update the connection so my tables pull from the new source.

Gilson on 2019-06-12 14:38:06
Hello. The function Web Service () does not work correctly. When I execute the link everything is ok, but the cells Response and Distance can not show the results.

Mohammed on 2019-06-12 11:48:18
Hi Chandoo. I wanna ask how to count the numbers of training in each affiliate and warehouse. and the formula will be in other sheet of excel. please see my below table Warehouse Training Name Training Type Safco SHEM 10 Affiliate Hadeed SHEM 12.06 Affiliate Ibb SHEM 01 Warehouse Albayroni SHEM 11 Warehouse

Salim Talal on 2019-06-12 07:10:48
Chandoo, this is fantastic, you really saved my day...... absolutely unbelievable. All of my years with Excel I never knew this was possible...... Really great!!!

Chandoo on 2019-06-10 23:12:03
Hi H This is how you can do it. Create a third table with all slicer options (in this case it would be Name column) with one row per unique value. Now add this table to your source list. Then link all two tables via this third table thru Data ribbon > Manage relationships feature. Finally add a slicer on this third table column and link the slicer to both pivot charts. Please note that you need to construct the tables and charts after data model is created. See this page for more explanation on how to use relationships - https://chandoo.org/wp/introduction-to-excel-2013-data-model-relationships/

Juan Pablo on 2019-06-10 17:01:23
Jeez! such a good work behind this. Thanks!

Vasanth on 2019-06-10 06:27:51
WOW.............!!!! Lovely Bro, Love You :-)

RAJIV SIROHI on 2019-06-09 05:21:44
=SUMPRODUCT(COUNTIF(List B, List A))

Madhu Ramji on 2019-06-06 17:38:56
How to resize checkbox not it's frame

RAJIV SIROHI on 2019-06-06 11:21:43
{=IF(AND(A1:A9=A2:A10),"EQUAL","NOT EQUAL")} this is an array formula, insert it with CSE.