arkusM
Well-known Member
- Joined
- Apr 12, 2007
- Messages
- 560
Hello all,
I started the below thread a while ago, and was able to resolve the issue I had.
http://www.mrexcel.com/forum/showthread.php?t=340445
But, I am curious as to why excel crashes when I put the volatile functions in one forula, but when I break the same amount of volatile functions into named formulas it works. In fact there is another volitle function in the conditional formatting, the was added after I got the formula to work. If there is a limit on volatility then why can it be circumvented? I know that you can do similar tricks with nested if statements.
Generally I do not understand that if mashing all the functions together crashes excel because there are too many, how/why breaking them up does not cause the same problem… This may just be my ignorance speaking but it seems odd.
It is kinda like sleeping with your co-worker is bad if it happens during work hours, like lunch time. But if it happens before work and after work it is ok. I know there is a lot of gray in that scenario, that is life.
The computer world, I thought, as much more binary. If it is an illegal operation, it can’t be done. Period. With my case in the above OP, it seems that Excel has some non-binary solutions. Has MS built some human-like AI that allows Excel to operate in the gray!!?!?!
In case you were wondering; the idea was to do a vlookup on a value and if there was an error create a dynamic range, based on a helper column, that sumed up a specified number in the helper column. Say from current postion up 14 rows give me the sum.
The below crashes Excel.
The below does not:
For formatting
I started the below thread a while ago, and was able to resolve the issue I had.
http://www.mrexcel.com/forum/showthread.php?t=340445
But, I am curious as to why excel crashes when I put the volatile functions in one forula, but when I break the same amount of volatile functions into named formulas it works. In fact there is another volitle function in the conditional formatting, the was added after I got the formula to work. If there is a limit on volatility then why can it be circumvented? I know that you can do similar tricks with nested if statements.
Generally I do not understand that if mashing all the functions together crashes excel because there are too many, how/why breaking them up does not cause the same problem… This may just be my ignorance speaking but it seems odd.
It is kinda like sleeping with your co-worker is bad if it happens during work hours, like lunch time. But if it happens before work and after work it is ok. I know there is a lot of gray in that scenario, that is life.
The computer world, I thought, as much more binary. If it is an illegal operation, it can’t be done. Period. With my case in the above OP, it seems that Excel has some non-binary solutions. Has MS built some human-like AI that allows Excel to operate in the gray!!?!?!
In case you were wondering; the idea was to do a vlookup on a value and if there was an error create a dynamic range, based on a helper column, that sumed up a specified number in the helper column. Say from current postion up 14 rows give me the sum.
The below crashes Excel.
Rich (BB code):
=SUM(INDIRECT(ADDRESS(ROW()-INDIRECT(ADDRESS((ROW(),7)),COLUMN())),INDIRECT(ADDRESS(ROW()-1,COLUMN())))The below does not:
Rich (BB code):
=LOOKUP(9.99999E+307,CHOOSE({1,2},SUM(Top_RNG:T_End_Rng),Volume_Lookup))
Rich (BB code):
Top_Rng =INDIRECT(ADDRESS(ROW()-1,COLUMN()))
T_End_Rng =INDIRECT(ADDRESS(ROW()-T_Col,COLUMN()))
T_Col =INDIRECT(ADDRESS(ROW(),7))
Volume_Lookup =VLOOKUP(Revenue!$F27,Rec_PTS_MN,Revenue!N$1,0)For formatting
Code:
=MOD(ROW()-14,1*2)+1<=1
Last edited:
