• 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. J

    No of days between two dates

    Hi BM, Excel date values are coded in a consecutive number system, so you can subtract one date from the other with the result being the number of days in between. Your values need to be formatted as dates. Hopefully this helps, otherwise post a link to your spreadsheet so we can have a look.
  2. J

    Weighted average in pivot tables

    Bonjour Deb, Thanks for responding! The average I'd like to get will be calculated by the Pivot Table (option "Average" for "Display value as") as opposed to manually. The Level1 and Level2 are not guaranteed to be sorted. Pivot table averages are by default equally weighted. But when...
  3. J

    Shorten Sumproduct formula

    Goodness! How did I miss that one. Merci (aka "Thank you" in French) Deb :) Jesse
  4. J

    Shorten Sumproduct formula

    Hi there, how can we shorten this? =SUM(SUMPRODUCT(A1:W1,A11:W11),SUMPRODUCT(A2:W2,A12:W12),SUMPRODUCT(A3:W3,A13:W13),SUMPRODUCT(A4:W4,A14:W14)) It's summing the sumproduct of 4 paired arrays.. I've tried using MMULT with TRANPOSE, but it doesn't give me the same result...
  5. J

    Weighted average in pivot tables

    Hi Narayank991, my apologies. The posted link counts the number of unique Level2's within each Level1, and displays it on each row. I use this in my calculation of the weighted average. From the data above, example output: Level1 Level2 Average Water Quantity =AVERAGE(31,23) = 27...
  6. J

    Transpose table from a questionnaire

    Wow, thank you! I feel enlightened (and a little lighter also). I really appreciate it :)
  7. J

    post not showing up? posts being modded?

    Great, thanks Hui! (Maybe I'm asking too much at once...)
  8. J

    post not showing up? posts being modded?

    I'm having the same problem - tried to start a new topic, along with replies. Any help, or what could I do?
  9. J

    Weighted average in pivot tables

    Greetings again! I know Pivot Tables can calculate averages, sums, etc., but can they calculate weighted averages? Below is a table with categories (Level1) and Sub-categories (Level2). Within each sub-category, I want the average (simple enough). For the category (Level1) I want the...
  10. J

    Transpose table from a questionnaire

    Hi all, I'm creating a questionnaire, which will be linked to a small dashboard. I'd need to transpose a table that maintains links to the original table. I've tried to many things! Here's a file that explains more and shows my plan so far. I originally posted a related question here...
  11. J

    Combining paste functions: Paste Link and Transpose

    Anyone able to do this? Choosing "transpose" in the Paste Special menu grays out the Paste Link option. AND/OR I have a reasonable array of cells (~15 rows x 24 cols), populated by a conditional reference to another cell: =IF(C6="", "", C6) - I want to transpose paste this...
  12. J

    Change Icon

    Great, thanks!
  13. J

    Change Icon

    Hi there, is it possible to change my icon/picture? My face looks stressed out, it makes me feel tense just looking at it! Many of your faces look so nice and calm; your tranquility is contagious.
  14. J

    Creating a modeless UserForm to guide users as needed

    Great, thanks Narayank991 (and SirJB7!). I looked at some of their source code and found that the password to unprotect sheets is "SunShine". Anyways, I had a thought that may simplify this, if it can be done. Idea #1: Have a static text in a TextBox in a column to the left, and have a button...
  15. J

    Creating a modeless UserForm to guide users as needed

    Hi there, I'm creating an assessment tool (to evaluate water security in rural, resource-limited communities) and would like to provide guidance As Needed by the users. I'd also like to save screen space and not overwhelm the user, so I'd like to rule out using simple text boxes. My...
  16. J

    Display name of a named range in cell

    Thanks for the quick reply. I didn't think it was possible, but I thought I'd check. I'm creating an assessment tool, and there are lots of named ranges, and so I'm trying to minimize the maintenance burden for future developers by using INDIRECT(CellAddress_holding_RangeName). Thanks again :)
  17. J

    Display name of a named range in cell

    Hello, I want to display the name of an assigned range in a cell. Example: Cell $B$5 is named Population Example: Range $C$9:$E$20 is named Stats I want to display the assigned name within a cell (say A1), so that the cell output reads, e.g. Population. If I were to change the name of the...
  18. J

    Count unique values within a dynamic hierarchal range

    Found something! http://chandoo.org/forums/topic/i-need-to-count-the-number-of-unique-values-in-a-list-given-a-variable-criteria Which links to this file with the solution: https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21119 (Link to spreadsheet example...
  19. J

    Count unique values within a dynamic hierarchal range

    Hi there, I've read up on counting unique number of entries: sumproduct(1/countif(A2:A100, A2:A100)) What I'm trying to do now is do this for a dynamic subset of this range. I'm using Excel's Table features, and it's set up like this: Level1 Level2 Count of Unique Level2 for...
  20. J

    Hello .. Introduce yourself

    Hey! I've been working with Excel on and off in my university studies. I'm currently working on finishing my master's thesis at McMaster University in Canada. My research deals with water security in rural, resource-limited communities. I'm developing a self-assessment tool (in Excel) as a means...
Back
Top