Search results

Good solutions @John Jairo V, @excelent :) Here is another one. =IF(ROW()>$A$1*COUNTA(B:B),"",IF(B1=0,OFFSET(C1,-COUNTA(B:B),0),B1)) PS: this assumes that formula starts from row 1 all the time.

Awesome solution @Chihiro Out of curiosity, I experimented with other alternatives. Here is one that tallies the results (not sure if it will work for all conditions). =IFERROR(LOOKUP($A$1,C3:J3,D3:K3),C3)-$A$1

I think @cocabaldi worksheet had formulas in multiple cells that look at year & n values to calculate the dates. See here for an example on how to do this http://chandoo.org/wp/2010/04/06/rolling-months/

It depends on how you want to treat the blanks. If Finished Work date is blank, then you can assume the activity is still going on. If Started Work date is blank, then you can assume the activity started at date 0 (or the minimum of the dates in first rows). If you go with these assumptions...

This is not possible with conditional formatting alone. One option is to mix CF with a picture link, but this may not look crisp and doesn't work well when printing. I think using VBA (or a lot of CF rules) is the easiest option here.

Does your colleague have a different language version of Excel 2010? For example in French version of Excel, FIND() is TROUVE()

Good solution @Nebu Another variation is to use 2 dummy values both at start and end of series to cheat the area chart and stretch it all the way. See attached workbook.

@shaikhrulez You have too many values to compare the KPI for current month. I say, select 1 or 2 important values to compare (either narrow down the list or use form controls so that user can tell what they want to compare the KPI with). Then use Thermometer chart to show how the KPI is doing...

As @chirayu suggested, when you need to print the reports, you end up with fewer options. Here is one using dot plots that tries to reduce the complexity of your data. Check out the download workbook.

Interesting question and cool answer @Nebu :) Here is one more variant. =SUMPRODUCT( MMULT(($A$3:$A$12=$A20)*1,($B$1:$N$1=J$19)*1),$B$3:$N$12 ) This one uses MMULT to generate a 2D array of 0s & 1s, 1 where the corresponding values in B3:N12 should be added up and 0 for values to be omitted...

Do this: In F5 paste the formula =IF(ISBLANK(D5),TODAY()-C5,D5-C5) Select E5, go to Home > Conditional Formatting > Manage rules Edit the traffic light rule Click on Show icon only. Now you have both traffic light and the value in 2 separate cells.

Hui, awesome formula... Here is one more variation, that avoids hardcoding of the values in CHOOSE. =DEC2BIN(LARGE($A$26:$A$281*$C$26:$C$281, RANDBETWEEN(1,70)),8)

Hi Omar... Thanks for your message and interest in our products. We do not have any black Friday or cyber Monday sale. Since the dashboard templates are priced to offer excellent value for money we are unable to offer further discounts on it. Regarding the Exchange rate, this is not controlled...

Hi Muhammad... Please use below links to pay for the ebook. Ebook only: http://chandoo.org/checkout-india/tvb-ebook.html and Ebook + Video combo: http://chandoo.org/checkout-india/tvb-combo.html

Hi Guity, I think in that video I was using Excel 2013. As far as I know or remember, Power View is a feature in 2013. MS has few beta releases for older versions (again not sure).

IF what you want is to show only top 10% of values after arranging in descending order, then Follow below steps: Once the running total field is added to pivot, go to the row label, click on filter button Choose value filter > top 10 Specify Top 10 percent of sum of values as criteria Click...

Great thread and interesting discussion. Since I had a few minutes to kill, I made a small workbook that can show userform (with answer choices) upon question selection. It automatically records the response to a database worksheet (along with a studentID, timestamp) for further logging or...

Hi Guity, Power View is a separate feature that is part of Excel 2013. I do not think it is available for Excel 2010.

Hi there, I am happy to hear that you like our work and want to support the ninjas. Please take a minute to choose a ninja for donation (yes, you can donate to more than one). Luke M Hui VLETM Keep this in mind while donating: 100% of your donations go to the ninja you are donating...

+1 Please do that Somendra. Many will benefit from your instruction.

What is the duration to spread this amount. Is it the months prior or after the order date? If it is prior, you can use a formula like this. Assuming your data starts is in A2 (row 2 has headers, row 3 has values), like this: =IF(C$2<=$A3, $B3/((YEAR($A3)-YEAR($C$2))*12 +...

@kumar... The big challenge as per your post is avoiding circular references. Once you paste my formula in your workbook, you can adjust rest of the calculations (like ESI changes etc.), the numbers would re-calculate and total would be same as CTC offered. Try that and let us know if you have...

@Kumar@raja Welcome to Chandoo.org forums and thanks for your question. You can remove the circular reference by re-stating the equation for Special Allowance. for your data, you can use =(C5-(1.1*SUM(C7:C10)+SUM(C15:C18)))/1.1 as the formula for this. Explanation: Assuming Y is CTC you want...

Hi Apollo2000, Welcome to Chandoo.org forums and thanks for posting your question. I assume by Pivotol you mean Pivot tables. You can do one of the 2 things: 1. Use report filters to exclude items you do not want. See http://chandoo.org/wp/2011/04/20/pivot-table-report-filters/ for help on...

Thanks for purchasing our templates and sorry to hear that you could not use them. Your refund is processed on April 25th.