• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. juanito

    Capital Investment Appraisal Model

    I'm trying to find a solution for this: my company's current model is using a dangerously old Excel version (developed 1997!!). I'm finding it surprisingly hard to find one, especially ones for sale where the provider guarantees upgrades and bug fixes for a defined period. (There are plenty of...
  2. juanito

    Weighted average in flat file

    Hi all - I want to find a formula which can extract a weighted average where there is only one Values column. I can do this by pivoting into two columns easily enough (see solution to the right), but prefer to find a formula which can act directly on the data table as presented... and I haven't...
  3. juanito

    Named formula mystery

    I have a named formula for dynamic range "_dq" defined as follows: =OFFSET(qualdata!$A$2;;;COUNTA(qualdata!$A:$A)-1;COUNTA(qualdata!$1:$1)) I am getting #NAME? errors referencing this formula from other worksheets. If on the 'qualdata' source worksheet, I can type F5 go to, "_dq" and the...
  4. juanito

    Hit the wall with INDEX

    I have an 2D array - in the name manager, not the grid. My model uses INDEX to return discrete columns of this array. So if the array's name is "d", then to return the third column I have: INDEX(d,,3) (This is using the awesome power of omitting the row argument in INDEX - you get them all!)...
  5. juanito

    Stretching a vector

    Hi - I have a vector that I'm feeding into a chart. Let's say it's {1,3,4}. Because of the way the chart's set up I need to transform the vector into {1,0,0,3,0,0,4,0,0} - so I'm inserting two zero values between each vector element. Any ideas how to do this? Thanks, Juanito
  6. juanito

    Reverse 2D Lookup

    I have a table of values with headings along the top and down the left-hand side. Months and soccer teams respectively, for instance. I need a formula which returns both headings of the largest value. Thought this would be easy-ish, but I'm having a bad day... Any Forum help will be much...
  7. juanito

    Text Box Print Issue

    Hi - I have a simple report with commentary below in a text box which is print-enabled. I tell Excel to print the whole sheet, center horizontally, sizing to one sheet landscape and so on - all pretty elementary. Problem is, the report isn't centered horizontally - it's forced to the left...
  8. juanito

    Waterfall chart question

    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...
  9. juanito

    Pivot tables - moving columns around

    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...
  10. juanito

    Array-entered SUMPRODUCT

    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...
  11. juanito

    Mass-change border hue

    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...
  12. juanito

    Converting files to values

    I have a folder with a lot of Excel files, of the coporate year-end reporting sort. I need to convert all of them to values. Do I really have to do this workbook by workbook, sheet by sheet? Any shortcuts please?
Back
Top