Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Temp variables in formulas?
Here's a common example: you want a formula with a VLOOKUP which either looks
up the number you want, or returns zero if it's not in the table -- this is so that calculations based on that formula use zero and calculate something meaningful rather than result in #N/A!. So you have to do this: IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) Of course that "..." can be pretty long and hairy. And it's inefficient too, because that long VLOOKUP has to be calculated twice. Isn't there any way of writing a formula so it calculates the VLOOKUP once, stored the result, and uses just the result in the rest of the formula? That is: R=VLOOKUP(...);IF(ISNA(R),0,R) I realize you can insert columns just to hold temporary results, and then hide them, but when you copy them and paste them outside Excel, you get the hidden columns too, and I don't want them. Thanks! -- Andy Smith Senior Systems Analyst Standard & Poor's, NYC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Temp variables in formulas?
I provided an answer to this question in the other newsgroup where you
posted it. You should really only post your questions in one newsgroup (usually the one applicable to your question... this question is not really a "programming" question). Otherwise you end up wasting the time of the volunteers here... if you ask in more than one newsgroup, and it gets answered in that other newsgroup, someone wanting to help wouldn't know that and might waste their time answering it again here. -- Rick (MVP - Excel) "Andy Smith" wrote in message ... Here's a common example: you want a formula with a VLOOKUP which either looks up the number you want, or returns zero if it's not in the table -- this is so that calculations based on that formula use zero and calculate something meaningful rather than result in #N/A!. So you have to do this: IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) Of course that "..." can be pretty long and hairy. And it's inefficient too, because that long VLOOKUP has to be calculated twice. Isn't there any way of writing a formula so it calculates the VLOOKUP once, stored the result, and uses just the result in the rest of the formula? That is: R=VLOOKUP(...);IF(ISNA(R),0,R) I realize you can insert columns just to hold temporary results, and then hide them, but when you copy them and paste them outside Excel, you get the hidden columns too, and I don't want them. Thanks! -- Andy Smith Senior Systems Analyst Standard & Poor's, NYC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Temp variables in formulas?
You have a response at your other post.
Andy Smith wrote: Here's a common example: you want a formula with a VLOOKUP which either looks up the number you want, or returns zero if it's not in the table -- this is so that calculations based on that formula use zero and calculate something meaningful rather than result in #N/A!. So you have to do this: IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) Of course that "..." can be pretty long and hairy. And it's inefficient too, because that long VLOOKUP has to be calculated twice. Isn't there any way of writing a formula so it calculates the VLOOKUP once, stored the result, and uses just the result in the rest of the formula? That is: R=VLOOKUP(...);IF(ISNA(R),0,R) I realize you can insert columns just to hold temporary results, and then hide them, but when you copy them and paste them outside Excel, you get the hidden columns too, and I don't want them. Thanks! -- Andy Smith Senior Systems Analyst Standard & Poor's, NYC -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Temp variables in formulas?
It's a personal thing but I dislike very long single cell formulas, and
split it up into two or more cells. Apart from being more efficient it's much easier to figure what it all does when you come back six months later. So with your example put the lookup formula into a helper cell and refer to that cell. Regards, Peter T "Andy Smith" wrote in message ... Here's a common example: you want a formula with a VLOOKUP which either looks up the number you want, or returns zero if it's not in the table -- this is so that calculations based on that formula use zero and calculate something meaningful rather than result in #N/A!. So you have to do this: IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) Of course that "..." can be pretty long and hairy. And it's inefficient too, because that long VLOOKUP has to be calculated twice. Isn't there any way of writing a formula so it calculates the VLOOKUP once, stored the result, and uses just the result in the rest of the formula? That is: R=VLOOKUP(...);IF(ISNA(R),0,R) I realize you can insert columns just to hold temporary results, and then hide them, but when you copy them and paste them outside Excel, you get the hidden columns too, and I don't want them. Thanks! -- Andy Smith Senior Systems Analyst Standard & Poor's, NYC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Temp variables within formulas? | Excel Discussion (Misc queries) | |||
Temp vaiables in formulas? | Excel Worksheet Functions | |||
Problem using obj.Namespace(sourceFileName) Namespace(DestinationFilename).Itemsand then opening the xls file which creates a Temp Folder inside Temp | Excel Discussion (Misc queries) | |||
variables in formulas | Excel Programming | |||
How can I use variables in formulas in VB? | Excel Discussion (Misc queries) |