Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula do not work until edited | Excel Discussion (Misc queries) | |||
IF / VLOOKUP formula won't work until saved | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions |