Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 14th 05, 07:50 PM posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default 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   Report Post  
Old December 14th 05, 08:03 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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.



  #3   Report Post  
Old December 14th 05, 09:56 PM posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default 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   Report Post  
Old December 14th 05, 11:41 PM posted to microsoft.public.excel.worksheet.functions
William Horton
 
Posts: n/a
Default 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   Report Post  
Old December 15th 05, 01:30 AM posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default 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.






  #6   Report Post  
Old December 15th 05, 01:42 AM posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default 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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula do not work until edited KiwiSteve Excel Discussion (Misc queries) 12 November 8th 05 10:45 AM
IF / VLOOKUP formula won't work until saved tawtrey(remove this )@pacificfoods.com Excel Worksheet Functions 2 August 4th 05 11:55 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
formula won't work tink13ub Excel Worksheet Functions 1 January 17th 05 07:59 AM
formula won't work Linette Excel Worksheet Functions 0 January 17th 05 07:05 AM


All times are GMT +1. The time now is 02:25 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017