Contact Me 20

As well as through comments to the various blogposts, I would be more than happy to answer any questions, solve any problems, etc. from readers looking for a formula-based solution to a particular situation they may be faced with: just drop me an e-mail at XORLX99@GMAIL.COM

Of course, any other comments, suggestions for improvement, etc. are also welcome. And if anyone has a particular topic in mind which they’d wish to see a post on, please let me know and I’ll do my best to accommodate that wish.

20 comments

  1. hi, I love the ideas & the innovation you have here on your blog. My aged eyes however can’t keep up with the text being almost the same [color/shade/ hue/whatever its called] as the background. Could you please make it easier for your older readers?

  2. Hi Neil and welcome!

    Thanks for your kind words. I also appreciate your comment re the layout and it is something I am working on right now. Hopefully I will have a new format at some point in the not-too-distant future.

    Regards

  3. wwwww XOR LX you are absolutly the best exceller that I know

    I saw your formula in the forum and in this web site… absolutly ADVANCE you have a solution for every thing….

    I don’ t know if you are an Excel MVP in case not you deserve it a soon as possible

  4. @GerryZ 🙂

    You’re too kind, my friend! I just try to do what I can.

    And thank you for your very kind words. Much appreciated.

  5. Hello:

    I’ve heard of the ROW(INDEX:INDEX) formula but can’t find any reference to it’s use in practice.

    Can you please enlighten me?

    Regards,

    Vic

  6. Hi Vic, and thanks for your query.

    Personally I use that construction almost exclusively in place of the (sadly more common, yet volatile) equivalent ROW/INDIRECT set-up.

    In fact, I used it in my most recent post:

    https://excelxor.com/2016/03/07/molecular-weights/

    What precisely is it about this construction that you’re having difficulty understanding?

    To take a simple example, in order to generate a dynamic array of integers from 1 up to the value in cell A1, we could use the aforementioned volatile ROW/INDIRECT set-up, viz:

    ROW(INDIRECT("1:"&A1))

    though the (almost) non-volatile ROW/INDEX set-up is preferable, in my opinion:

    ROW(INDEX(A:A,1):INDEX(A:A,A1))

    the point being that, in this usage (and assuming that A1 contains, for example, 10):

    INDEX(A:A,1)

    and:

    INDEX(A:A,A1)

    resolve to the cell references:

    $A$1

    and:

    $A$10

    respectively, and not to the actual cell contents of those references (INDEX is one of the very few functions that has this wonderful dual property of being able to return both a cell reference and a cell content, depending on how the expression is constructed).

    Which means that:

    ROW(INDEX(A:A,1):INDEX(A:A,A1))

    resolves to:

    ROW($A$1:$A$10)

    i.e.:

    {1;2;3;4;5;6;7;8;9;10}

    as required.

    Hope that helps.

  7. Hello XOR LX,

    Many thanks for your quick reply.

    With that, plus a read-through of Molecular Weights and another look at the INDEX syntax, all is now clear.

    Congratulations on an excellent site.

    Regards,

    Vic

  8. hi XORLX…hreat site I found. Need to learn a lot more to appreciate your advanced level. I have emailed you a quick question. Would greatly appreictae if you could help

  9. How to register on this site/blog?? Can i direct to another forum for my excel VBA question?

  10. Hi there,

    How to you take this formula for multiple criteria & get it to work with filtered cells?

    =COUNTIFS(B2:B14,"Male",C2:C14,"Sea lion")
  11. @Rich

    In that case you would need to switch to SUMPRODUCT:

    =SUMPRODUCT(0+SUBTOTAL(3,OFFSET(B2,ROW(B2:B14)-MIN(ROW(B2:B14)),)),0+(B2:B14="Male"),0+(C2:C14="Sea lion"))

    Hope that helps!

    Regards

  12. I’m trying to expand on it, what if I wanted it look at a range of time?

    =COUNTIFS(S2:S37146,">=00:00",S2:S37146,"="00:00"),0+(S2:S37146<="00:59"))
  13. Sorry, the previous post didn’t post correctly.Let’s try again..

    I’m trying to expand on it. What if I want it to look at a range of time?

    =COUNTIFS(S2:S37146,">=00:00",S2:S37146,"="00:00"),0+(S2:S37146<="00:59"))
  14. =SUMPRODUCT(0+SUBTOTAL(3,OFFSET(S2,ROW(S2:S40675)-MIN(ROW(S2:S40675)),)),0+(S2:S40675>="00:00"),0+(S2:S40675<="00:59"))

    Thanks.

  15. There’s nothing syntactically wrong with your formula. Why aren’t the results you are getting what you would expect? Are the values in that range formatted as proper time values?

    Perhaps you can share a link to the actual workbook if you still can’t get it to work.

    Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s