Remember Me? December 14th 05, 06:50 PM posted to microsoft.public.excel.worksheet.functions
 Kleev Posts: n/a 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. December 14th 05, 07:03 PM posted to microsoft.public.excel.worksheet.functions
 Peo Sjoblom Posts: n/a 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" 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. December 14th 05, 08:56 PM posted to microsoft.public.excel.worksheet.functions
 Kleev Posts: n/a 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. December 14th 05, 10:41 PM posted to microsoft.public.excel.worksheet.functions
 William Horton Posts: n/a 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. December 15th 05, 12:30 AM posted to microsoft.public.excel.worksheet.functions
 Kleev Posts: n/a 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" 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. December 15th 05, 12:42 AM posted to microsoft.public.excel.worksheet.functions
 Kleev Posts: n/a 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post KiwiSteve Excel Discussion (Misc queries) 12 November 8th 05 09:45 AM tawtrey(remove this )@pacificfoods.com Excel Worksheet Functions 2 August 4th 05 11:55 PM sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM tink13ub Excel Worksheet Functions 1 January 17th 05 06:59 AM Linette Excel Worksheet Functions 0 January 17th 05 06:05 AM

All times are GMT +1. The time now is 09:10 PM. Copyright ©2004-2020 ExcelBanter.