+ Reply to Thread
Results 1 to 6 of 6

Making this formula work

  1. #1
    Kleev
    Guest

    Making this formula work

    Based on a question in a different forum, I was trying to see if I could
    figure out the answer using some of the new techniques I've seen on these
    forums. I wrote the following formula, which I can't seem to get to work,
    although using F9 to calculate parts of the formula seem to indicate it
    should work. Can someone point me in the right direction, assuming what I am
    trying is possible.
    My formula is:
    =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))

    In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
    respectively.

    CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
    {"G10","J10","M10","P10"}.

    Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
    {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I enter
    this formula in another cell, I get 1000. However, if I use the original
    formula, no matter whether I enter it normally or as an array formula, I get
    100. What's up with that?

    This appears to be the step that it is failing on as it returns 100, but I
    don't know how to fix it or if it is doable:
    =SUM(INDIRECT({"g10","j10","m10","p10"}))

    Can anyone shed any light as to if this is doable like this and if so, what
    I need to change in order to make it work? Thanks.

  2. #2
    Peo Sjoblom
    Guest

    Re: Making this formula work

    Use

    =SUM(N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")))

    or if you just want to sum every third cell from G10 to P10

    =SUMPRODUCT(--(MOD(COLUMN(G10:P10),3)=1),G10:P10)



    --

    Regards,

    Peo Sjoblom

    "Kleev" <[email protected]> wrote in message
    news:[email protected]...
    > Based on a question in a different forum, I was trying to see if I could
    > figure out the answer using some of the new techniques I've seen on these
    > forums. I wrote the following formula, which I can't seem to get to work,
    > although using F9 to calculate parts of the formula seem to indicate it
    > should work. Can someone point me in the right direction, assuming what I

    am
    > trying is possible.
    > My formula is:
    > =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))
    >
    > In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
    > respectively.
    >
    > CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
    > {"G10","J10","M10","P10"}.
    >
    > Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
    > {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I

    enter
    > this formula in another cell, I get 1000. However, if I use the original
    > formula, no matter whether I enter it normally or as an array formula, I

    get
    > 100. What's up with that?
    >
    > This appears to be the step that it is failing on as it returns 100, but I
    > don't know how to fix it or if it is doable:
    > =SUM(INDIRECT({"g10","j10","m10","p10"}))
    >
    > Can anyone shed any light as to if this is doable like this and if so,

    what
    > I need to change in order to make it work? Thanks.




  3. #3
    Kleev
    Guest

    RE: Making this formula work

    I appreciate the response. If it won't work the way I originally had it or
    with a slight variation thereof, then it won't work. In my opinion, using
    the individual Indirect's is too unwieldy and defeats the purpose of my
    trying to use them in this way. But thanks.

    The following formula is probably what I would have come up with had I
    gotten my original formula to work:

    =SUMIF(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10"),"
    >=4",(INDIRECT(CHAR({7,10,13,16} + 64) &

    "10")))/COUNTIF(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10"), ">=4")

    The OP in the other forum (General Questions) was asking for a way to
    calculate an average based on the cell to its right being >= 4 (or something
    along those lines.)

    That seems (to me) to be close, but the answer is 0 (which I know is not
    right.)


    "William Horton" wrote:

    > I believe your CHAR function is returning an array. If you highlite 4
    > adjacent cells when you type your formla and then enter it with
    > control-shift-enter you will get 100, 200, 300, and 400 in those 4 cells. If
    > you want the answer in just one cell I think you will have to make one
    > indirect formula for each cell you are adding.
    >
    > Try this formula:
    > =SUM(INDIRECT(CHAR(71)&"10"),INDIRECT(CHAR(74)&"10"),INDIRECT(CHAR(77)&"10"),INDIRECT(CHAR(80)&"10"))
    >
    > Hope this helps.
    >
    > Thanks,
    > Bill Horton
    >
    > "Kleev" wrote:
    >
    > > Based on a question in a different forum, I was trying to see if I could
    > > figure out the answer using some of the new techniques I've seen on these
    > > forums. I wrote the following formula, which I can't seem to get to work,
    > > although using F9 to calculate parts of the formula seem to indicate it
    > > should work. Can someone point me in the right direction, assuming what I am
    > > trying is possible.
    > > My formula is:
    > > =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))
    > >
    > > In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
    > > respectively.
    > >
    > > CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
    > > {"G10","J10","M10","P10"}.
    > >
    > > Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
    > > {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I enter
    > > this formula in another cell, I get 1000. However, if I use the original
    > > formula, no matter whether I enter it normally or as an array formula, I get
    > > 100. What's up with that?
    > >
    > > This appears to be the step that it is failing on as it returns 100, but I
    > > don't know how to fix it or if it is doable:
    > > =SUM(INDIRECT({"g10","j10","m10","p10"}))
    > >
    > > Can anyone shed any light as to if this is doable like this and if so, what
    > > I need to change in order to make it work? Thanks.


  4. #4
    William Horton
    Guest

    RE: Making this formula work

    I believe your CHAR function is returning an array. If you highlite 4
    adjacent cells when you type your formla and then enter it with
    control-shift-enter you will get 100, 200, 300, and 400 in those 4 cells. If
    you want the answer in just one cell I think you will have to make one
    indirect formula for each cell you are adding.

    Try this formula:
    =SUM(INDIRECT(CHAR(71)&"10"),INDIRECT(CHAR(74)&"10"),INDIRECT(CHAR(77)&"10"),INDIRECT(CHAR(80)&"10"))

    Hope this helps.

    Thanks,
    Bill Horton

    "Kleev" wrote:

    > Based on a question in a different forum, I was trying to see if I could
    > figure out the answer using some of the new techniques I've seen on these
    > forums. I wrote the following formula, which I can't seem to get to work,
    > although using F9 to calculate parts of the formula seem to indicate it
    > should work. Can someone point me in the right direction, assuming what I am
    > trying is possible.
    > My formula is:
    > =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))
    >
    > In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
    > respectively.
    >
    > CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
    > {"G10","J10","M10","P10"}.
    >
    > Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
    > {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I enter
    > this formula in another cell, I get 1000. However, if I use the original
    > formula, no matter whether I enter it normally or as an array formula, I get
    > 100. What's up with that?
    >
    > This appears to be the step that it is failing on as it returns 100, but I
    > don't know how to fix it or if it is doable:
    > =SUM(INDIRECT({"g10","j10","m10","p10"}))
    >
    > Can anyone shed any light as to if this is doable like this and if so, what
    > I need to change in order to make it work? Thanks.


  5. #5
    Kleev
    Guest

    Re: Making this formula work

    Thank you very much. You answered my question, and I was able to adapt one
    of your solutions (after much time and toil) to do what I had originally set
    out to do. However, I don't think my answer ends up being any better than
    what the OP on the other forum started with, so will not post this answer
    there. But what I finally came up with is:

    =IF(SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0))=0,0,
    SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4,
    N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")),
    0))/SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0)))

    Probably could be much simplified, but I feel lucky to have gotten this to
    work.


    "Peo Sjoblom" wrote:

    > Use
    >
    > =SUM(N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")))
    >
    > or if you just want to sum every third cell from G10 to P10
    >
    > =SUMPRODUCT(--(MOD(COLUMN(G10:P10),3)=1),G10:P10)
    >
    >
    >
    > --
    >
    > Regards,
    >
    > Peo Sjoblom
    >
    > "Kleev" <[email protected]> wrote in message
    > news:[email protected]...
    > > Based on a question in a different forum, I was trying to see if I could
    > > figure out the answer using some of the new techniques I've seen on these
    > > forums. I wrote the following formula, which I can't seem to get to work,
    > > although using F9 to calculate parts of the formula seem to indicate it
    > > should work. Can someone point me in the right direction, assuming what I

    > am
    > > trying is possible.
    > > My formula is:
    > > =SUM(INDIRECT(CHAR({7,10,13,16} + 64) & "10"))
    > >
    > > In cells G10, J10, M10, and P10 I have valuse of 100, 200, 300, and 400
    > > respectively.
    > >
    > > CHAR({7, 10, 13, 16} + 64) & "10" processed with F9 gives
    > > {"G10","J10","M10","P10"}.
    > >
    > > Then if I press F9 on INDIRECT(CHAR({7,10,13,16} + 64) & "10"), I get
    > > {100,200,300,400}. So now my formula is =SUM({100,200,300,400}). If I

    > enter
    > > this formula in another cell, I get 1000. However, if I use the original
    > > formula, no matter whether I enter it normally or as an array formula, I

    > get
    > > 100. What's up with that?
    > >
    > > This appears to be the step that it is failing on as it returns 100, but I
    > > don't know how to fix it or if it is doable:
    > > =SUM(INDIRECT({"g10","j10","m10","p10"}))
    > >
    > > Can anyone shed any light as to if this is doable like this and if so,

    > what
    > > I need to change in order to make it work? Thanks.

    >
    >
    >


  6. #6
    Kleev
    Guest

    Re: Making this formula work

    On second thought, since you wouldn't be able to copy that and have it change
    based on what row you were on, I made the following modification to it.

    =IF(SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & ROW())) >= 4, 1, 0))=0,0,
    SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & ROW())) >= 4,
    N(INDIRECT(CHAR({7,10,13,16} + 64) & ROW())),
    0))/SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & ROW())) >= 4, 1, 0)))

    "Kleev" wrote:

    > Thank you very much. You answered my question, and I was able to adapt one
    > of your solutions (after much time and toil) to do what I had originally set
    > out to do. However, I don't think my answer ends up being any better than
    > what the OP on the other forum started with, so will not post this answer
    > there. But what I finally came up with is:
    >
    > =IF(SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0))=0,0,
    > SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4,
    > N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")),
    > 0))/SUM(IF(N(INDIRECT(CHAR({7,10,13,16} + 64 + 1) & "10")) >= 4, 1, 0)))
    >
    > Probably could be much simplified, but I feel lucky to have gotten this to
    > work.
    >
    >
    > "Peo Sjoblom" wrote:
    >
    > > Use
    > >
    > > =SUM(N(INDIRECT(CHAR({7,10,13,16} + 64) & "10")))
    > >
    > > or if you just want to sum every third cell from G10 to P10
    > >
    > > =SUMPRODUCT(--(MOD(COLUMN(G10:P10),3)=1),G10:P10)
    > >
    > >
    > >
    > > --
    > >
    > > Regards,
    > >
    > > Peo Sjoblom



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1