Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Horrible looking IF function....
I using the IF function to enter a certain value from another workbook into a cell, but if that value happens to be 0, to enter "" (blank). My reference is quite a long winded expression using VLOOKUP, and I find that I have to enter the expression twice within the formula, once in the first argument for IF followed by <0, and again in the second argument for IF, to show what I want in the cell if the first condition is true. Basically, something like this, itself has an IF function and VLOOKUP function amongst others... =IF([expression]<0,[expression],"") Its easy enough to copy and paste the expression, but I end up with a function that's 3 lines long in the cell window and it looks pretty hairy! Surely there must be a tidier way of doing this??? |
#2
|
|||
|
|||
Put your VLOOKUP expression in another cell, then reference this with the If
function. A1: VLOOKUP.... B1: =IF(A1<0,A1,"") -- Ian -- "rmellison" wrote in message ... I using the IF function to enter a certain value from another workbook into a cell, but if that value happens to be 0, to enter "" (blank). My reference is quite a long winded expression using VLOOKUP, and I find that I have to enter the expression twice within the formula, once in the first argument for IF followed by <0, and again in the second argument for IF, to show what I want in the cell if the first condition is true. Basically, something like this, itself has an IF function and VLOOKUP function amongst others... =IF([expression]<0,[expression],"") Its easy enough to copy and paste the expression, but I end up with a function that's 3 lines long in the cell window and it looks pretty hairy! Surely there must be a tidier way of doing this??? |
#3
|
|||
|
|||
Hadn't considered that! Would certainly work, but would double the number of
cells I have, and I have enough as it is! Was wondering if there was a way of doing it within a formula, in layman's terms, somthing like this: =[expression], unless equal to 0, then "" Not critical really, just frustrated by lengthy formulae.... "Ian" wrote: Put your VLOOKUP expression in another cell, then reference this with the If function. A1: VLOOKUP.... B1: =IF(A1<0,A1,"") -- Ian -- "rmellison" wrote in message ... I using the IF function to enter a certain value from another workbook into a cell, but if that value happens to be 0, to enter "" (blank). My reference is quite a long winded expression using VLOOKUP, and I find that I have to enter the expression twice within the formula, once in the first argument for IF followed by <0, and again in the second argument for IF, to show what I want in the cell if the first condition is true. Basically, something like this, itself has an IF function and VLOOKUP function amongst others... =IF([expression]<0,[expression],"") Its easy enough to copy and paste the expression, but I end up with a function that's 3 lines long in the cell window and it looks pretty hairy! Surely there must be a tidier way of doing this??? |
#4
|
|||
|
|||
You could always hide the rows or columns with the VLOOKUP in. You'd still
have the cells, but they wouldn't clutter the appearance up. Alternatively, post the VLOOKUP fromula to see if anyone here can trim it. -- Ian -- "rmellison" wrote in message ... Hadn't considered that! Would certainly work, but would double the number of cells I have, and I have enough as it is! Was wondering if there was a way of doing it within a formula, in layman's terms, somthing like this: =[expression], unless equal to 0, then "" Not critical really, just frustrated by lengthy formulae.... "Ian" wrote: Put your VLOOKUP expression in another cell, then reference this with the If function. A1: VLOOKUP.... B1: =IF(A1<0,A1,"") -- Ian -- "rmellison" wrote in message ... I using the IF function to enter a certain value from another workbook into a cell, but if that value happens to be 0, to enter "" (blank). My reference is quite a long winded expression using VLOOKUP, and I find that I have to enter the expression twice within the formula, once in the first argument for IF followed by <0, and again in the second argument for IF, to show what I want in the cell if the first condition is true. Basically, something like this, itself has an IF function and VLOOKUP function amongst others... =IF([expression]<0,[expression],"") Its easy enough to copy and paste the expression, but I end up with a function that's 3 lines long in the cell window and it looks pretty hairy! Surely there must be a tidier way of doing this??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |