Making this formula work

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kleev - 14 Dec 2005 18:50 GMT

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.

Reply to this Message

Peo Sjoblom - 14 Dec 2005 19:03 GMT

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)

Signature

Regards,

Peo Sjoblom

> 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
[quoted text clipped - 23 lines]
> 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.

Reply to this Message

Kleev - 15 Dec 2005 00:30 GMT

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.

> Use
>
[quoted text clipped - 35 lines]
> what
> > I need to change in order to make it work? Thanks.

Reply to this Message

Kleev - 15 Dec 2005 00:42 GMT

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)))

> 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
[quoted text clipped - 17 lines]
> >
> > =SUMPRODUCT(--(MOD(COLUMN(G10:P10),3)=1),G10:P10)

Reply to this Message

William Horton - 14 Dec 2005 22:41 GMT

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

Reply to this Message