Search results

Marc - bobhc's suggestion is a bit quicker than CONCATENATE but I believe you'll still need to use the TEXT function to get your number into the desired format. - juanito

Interesting thread, guys! Hui is right on the money when he talks about associated skills. Excel is a means to an end, not an end in itself. Other skills and experience let you see where you want to go and you then use Excel to get there - NOT the other way round. As for any stigma around...

Thanks, Senhor Vijay - good to hear from you! (I've decided to award the star forum gurus a title... more below) So we now have an example of array-entered SUMPRODUCT {outside} the array-required function (TRANSPOSE, in this case)... so that's a little different from Don Luke's first thoughts...

Well, as it happens the mighty sam of excelhero academy fame came up with an example just the other day! You have a range called NAME of various text values. You want to check if they're all unique or, conversely, if any is repeated. Here's how you do it (note curly brackets denote...

Thanks for the link, Luke. In the article you refer to (which I hadn't read), Jon says: "In Excel 2007 there is no way to change the up-down bar gap width from within the user interface, but you can do it with VBA..." I've recently upgraded to 2007: that's why I'd managed to adjust width...

I made a waterfall chart following Chandoo's method: create a line chart; add up-down bars which are the waterfall; make the line disappear. I now want to make the bars "thicker" - to reduce the gap between them. But I can't for the life of me figure out how to do this. If I go to the "format...

Hi Meedan - I've tried doing that, and also calculating the total in the source data. But my problem is the column order. I don't want the order you show at the end of your post - what I want is: Budget total; Actual total; budget month by month; actual month by month. Can it be done?

Hi - here's a simple-enough looking problem which I haven't found the answer to! My source data is organised in a fairly straightforward table: columns for GL account, cost centre, category (actual or budget), and then 12 columns for monthly values. The output format is also pretty basic...

Hi there - this strikes me as a good question and I don't know the answer... I'm posting this to get the thread back on the forum board in case anybody wants to chime in.

Thanks Hui. Fred - Hui's right (of course!), but you still may find my suggestion of interest if you're really not empowered to rearrange your data in a more logical way!

Fred - another possibility is to use SUMPRODUCT. If you put "vol", for example, in row 1 for all columns with a volume value then your formula for total volume could be: =SUMPRODUCT(B2:BP2,--(B1:BP1="vol")) It's just another way of making your workbook less error-prone when you add or remove...

Thanks Luke - looks my head exploded for no good reason! (Arrays can sometimes do that, as Chandoo warns sometimes.)

First of all, you can use the DATEDIF function to calculate the value for duration rather than hardcoding. Then, for each month, use two conditions (>= startmonth and <=endmonth) and multiply by the monthly value. You can used two nested IF functions or just...

Negative numbers in a pie chart? I don't think so! Also, if you want to show a trend (year on year) then a pie chart isn't your man. Pie charts (if you use them at all, but that's another story) are standalones, for parts-of-a-whole, such as a breakdown of your customer base between domestic...

Since reading fascinating stuff on this function on Chandoo and other blogs like excelhero, I've been using it for all sorts of conditional stuff instead of other functions like IF and COUNTIF and so on. Now SUMPRODUCT is, it seems to me, an array function that you don't need to array-enter...

Fred - Hui's formula is an elegant solution to your problem and these techniques (in this case, distinguishing between alternate columns) are incredibly useful in certain situations, and if you can learn them you can really gain competitive advantage in the workplace. However, another part of...

Vijay - don't trouble with writing the code: all I wanted to see was whether this apparently simple operation could be done with the standard UI... looks like it can't which I find odd but no big deal. Thanks

Vijay - yes, but what if my two cell borders are coloured red by selection not by default? How can I change these two borders (the example is trivial but should be illustrative) in one go?

Guys: when I said table I was referring to the layout of the report - not Table in the technical excel sense. Vijay, imagine this: cell A1 has a left-side red border, and cell A2 has a right-side red border. How can I change both border colours in one go? - Juanito

I should add that this isn't a big deal for me - it's more of a theoretical issue: one of those moments when you think, "there must be a quicker way to do this". - Juanito

I think (ie I think I read him say it somewhere) that Chandoo is considering including VBA in his excel school programme... but I dare say it'll take him a while to get round to it with everything else he has in the pipeline. There's always excelhero but that's probably too advanced for most...

Fred - thanks for your response but it doesn't work!

Hi - imagine we have a standard report in table format (just one sheet) with dark borders around many data groups. We thought that helped them stand out nicely, but now we have a new manager who wants to reduce "data-ink ratio": he wants us to keep all the same borders, but to change them to a...

Vijay - now works perfectly. You are awesome, just as Chandoo would wish!

Hui: I wasn't able to specify the name of "personnel.xlsx" (did you mean "personal") since it was saved by default. However, I saved the trivial macro as you suggested and it's "personal.xls" at the address I show a couple of posts above. I suspect that wasn't the issue and that we're good now...