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.

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" wrote in message
...
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.

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.

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.

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" wrote in message
...
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.

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