IF Function 05-21-10

I have this formula currently set up...

=VLOOKUP($A14,$B$7:$J$10,5)*W14

but I needit to not reflect the calculation if W14 has a "-" dash in the 
cell. I've attempted to add in a If formula to this but it isn't working out. 

Any sugguestions are greatly appreciated.
0
Utf
5/21/2010 4:10:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1147 Views

Similar Articles

[PageSpeed] 53

By "-" do you mean a minus number or an actual dash??
if(w14<0,"",doyourthing)

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"casdaq" <casdaq@discussions.microsoft.com> wrote in message 
news:123F94A8-9D83-4159-B642-070D684CB3A5@microsoft.com...
>I have this formula currently set up...
>
> =VLOOKUP($A14,$B$7:$J$10,5)*W14
>
> but I needit to not reflect the calculation if W14 has a "-" dash in the
> cell. I've attempted to add in a If formula to this but it isn't working 
> out.
>
> Any sugguestions are greatly appreciated. 

0
Don
5/21/2010 4:19:34 PM
Try:
=IF(W14<=0,"",VLOOKUP($A14,$B$7:$J$10,5)*W14)

"casdaq" wrote:

> I have this formula currently set up...
> 
> =VLOOKUP($A14,$B$7:$J$10,5)*W14
> 
> but I needit to not reflect the calculation if W14 has a "-" dash in the 
> cell. I've attempted to add in a If formula to this but it isn't working out. 
> 
> Any sugguestions are greatly appreciated.
0
Utf
5/21/2010 5:01:01 PM
THANK YOU THANK YOU THANK YOU!! That worked out perfactly!

"Ron@Buy" wrote:

> Try:
> =IF(W14<=0,"",VLOOKUP($A14,$B$7:$J$10,5)*W14)
> 
> "casdaq" wrote:
> 
> > I have this formula currently set up...
> > 
> > =VLOOKUP($A14,$B$7:$J$10,5)*W14
> > 
> > but I needit to not reflect the calculation if W14 has a "-" dash in the 
> > cell. I've attempted to add in a If formula to this but it isn't working out. 
> > 
> > Any sugguestions are greatly appreciated.
0
Utf
5/21/2010 5:34:03 PM
On May 21, 12:10=A0pm, casdaq <cas...@discussions.microsoft.com> wrote:
> I have this formula currently set up...
>
> =3DVLOOKUP($A14,$B$7:$J$10,5)*W14
>
> but I needit to not reflect the calculation if W14 has a "-" dash in the
> cell. I've attempted to add in a If formula to this but it isn't working =
out.
>
> Any sugguestions are greatly appreciated.

Put SUM(W14) instead of just W14.
0
jayray
5/21/2010 7:37:41 PM
Reply:

Similar Artilces:

1099 Limits in 10.0
Does anyone know why you can no longer enter a dollar threshold when printing 1099s in version 10.0? Or did that option get added to another window? Because now you enter a threshold amount for each box on the 1099 (Yay!). Click the 1099 Setup button on the Payables Setup Window. -- Leslie Vail, CPA, MCT, MVP MCBSP-Application for Microsoft Dynamics GP MCBSP-Installation and Configuration for Microsoft Dynamics GP cell: 972-814-8550 fax: 972-692-7472 "Jason" wrote: > Does anyone know why you can no longer enter a dollar threshold when printing > 1099s in version...

Function problems
I need to create a formula that can update when new entries and entered in the rows of a single page spreadsheet. Here is the problem. This is a standardized format for my office so all I can do is fix their equation. The prior year numbers are on the 2nd row of the spreadsheet, and they enter new entires in successive rows on the form so that they can track staff strength numbers on a weekly basis for the entire year. They want an equation that will recompute the weekly comparison to the prior year's ttl number. I can't just use SUM because we're not tracking growth. W...

UNDEFINED FUNCTION ERROR
Dear all, I had no problems with my access querries till i today. When i came to office and started daily reportw, querries having functions such as year, month, nz... become to give errors.. "undefined function nz.." We have no admin rights on PCs and i did not make any changes on my computer.. But, yesterday there had been an update in systems (but could not remember what it was...) and i guess there must be a relation. I am using English Windows, English Office but local settings for language is Turkish.. Regards, Hi SupperDuck, This sounds like a MISSING reference p...

Print Set-up is using only 10% of Page
I have a large document I am trying to have print out. The data I am printing is forced into about 10% of the page -- in the upper left hand corner -- rather than using the entire space available on the page. As a result, the data is too small to read. I had this document set up to print the way I wanted it -- using the entire page, and then at some point it got corrupted and crammed into the upper left hand corner. This happens to me quite often on this machine and on others. Thanks for any help. Excel 2000, XP. I would guess that on "Page Setup", you have the scaling checke...

Lab Value with iif function.
Hi, I am trying to use the iif function for laboratory values. Basically I have lab value with number and alphabets. I want all the values with an alphabet from A to Z to state "N" or negative and if it is a number value then leave it as is (no change). Below is an iif function I am trying to create maricles with. Please help. Thanks. TEST: IIf([lab_value]=like"*[A-Z]*","N") On Thu, 1 Apr 2010 17:27:01 -0700, E-mail report using Lotus Notes rather t <EmailreportusingLotusNotesrathert@discussions.microsoft.com> wrote: >Hi, > &...

USING CRM 02-21-06
I am new to MSCRM. I have installed CRM1.2 and Find that I have workflow monitor, workflow manager, and deployment manager. When I launch workfolow monitor or workflow manager I get the error, "the localhost is not responding. This might happen if is currently unavailable or is not a microsoft CRM Server. For more information, contact your system administrator". I try the ip address of the server and the server name and SQL Server name and get the same error. On the other hand I am not sure what I am supposed to be seeing. I though I followed the installation process ...

CoCreateInstance function returns NULL
Hi All, I'm trying to access some classes than an OLE server expose, I know the CLSID of such classes because they are placed in Windows Registry. I use the : STDAPI CoCreateInstance( REFCLSID rclsid, //Class identifier LPUNKNOWN pUnkOuter, //Object is or isn't part of an aggregate DWORD dwClsContext, //Context for running executable code REFIID riid, //Interface identifier LPVOID * ppv //Pointer to storage of interface pointer ); This is the program I use in Borland C++ 5 to instance a class LPVOID ppv; CLSID pclsidobjeto={0x12345678, 0x1234, 0x1234,0x1234,0...

Publisher 2007 04-06-10
I cannot insert a picture or the document numbers are not displayed at the bottom of the page. If you have 5 pages in your document, they are not numbered at the bottom so you can choose which one to work on. Kevin wrote: > I cannot insert a picture or the document numbers are not displayed > at the bottom of the page. If you have 5 pages in your document, they > are not numbered at the bottom so you can choose which one to work on. =============================== Have a look at the following article: (FWIW...it's always a good idea to create a System Restore point ...

Copy and Paste Macro 04-15-10
I am attempting to create a macro to copy and paste. The cells I need to copy are not adjacent to each other. I used the Ctrl key to select the cells but I get an error that it cannot be done. How do I select and copy these non-adjacent cells in a macro? http://j-walk.com/ss/excel/tips/tip36.htm -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "ABelect" <ABelect@discussions.microsoft.com> wrote in message news:98AECC0F-85E2-4390-98D6-D0CCAEC739FA@microsoft.com... >I am attempting to create a macro to copy and paste. The cells ...

User defined table function in XL2003
Hello all, I used to employ this feature many times earlier. My problem is: I would like to use a user defined function in Excel 2003. After having define a function, say Nrm(), in a module belonging to a table or to general, how can I make it available in my table? It does not appear in the list after INSERT > USER DEF. FUNCTION. Any help is greatly appreciated. Wolfgang The function should be in a General Module, not a sheet module or workbook module. So, in the VB Editor: Insert>Module. Type or paste your code there -- Kind regards, Niek Otten Microsoft MVP - Excel &qu...

Sort function for dates not working properly
I have a column with mm/dd/yy formatted for dates. When I sort by that column, it will sort by Jan-Dec 2004, then Jan-Dec 2003. I want it to do it by Jan 2004-Dec2003...how can I do this? Kayden, Excel will always use the year then the month to sort by dates. To sort by month ONLY, I would create another column and use the =MONTH() formula that extracts the month from your column of dates. After that, you can use this new column to sort using only the month. HTH Eric Hi Kayden If I got that right your result after sorting is, that you'll get the year descending and the month...

Excel hyperlinks
I have tried using nested functions in a hyperlink to another cell on different worksheet but keep getting the error that the specific fil cannot be found. Is it possible to use hypelink function with a nested function. I'm trying to lookup a value in cell A1 on a second sheet in the column and return the cell location so I can create a hyperlink to it. Please help. Thank -- Message posted from http://www.ExcelForum.com Rusty - In stead of a hyperlink, you could use a macro that uses If-Then or Select Case to go to another sheet or whatever, much as a hyperlink would. Trigger th...

OL hangs when performing any function.
When I open ol and try to send mail or any other function OL hangs an can only be closed using Ctrl Alt Del. I can ping all parties in th email chain fine. This appears to have happened after rnning ADWare Any ideas ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com PS I am running outlook 2000. When I finally close OL I have a messag that WMS is Running ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet me...

Multiple Users 05-19-10
I have a database which is used by at least 3 members of staff. They want me to look into improving the database because they keep getting messages preventing them from accessing and editing the database at the same time. The most annoying is when someone is making changes to the database and someone else tries to get on to look something up, but they get the message "The database has been placed in a state by user 'Admin' on machine 'PC11' that prevents it from being opened or locked." Really frustrating if you have a customer waiting for you to look...

Charttiltle as function
I have programmed a macro that makes a pivottable and a pivotdiagram. In this diagram I have a charttitle that I want to be equal to the content of one cell (D2). So if the content of the cell changes, I want the Charttitle also to change. Manually I can do this by selecting the charttitle and use the function (=D2) on the top of the excel-sheet. But this will not work when I try the recorder. Can anyone help? Are you running the macro each time the chart title changes? Barb Reinhardt "Trond" wrote: > I have programmed a macro that makes a pivottable...

Convert Excel function to Access Query
I have some raw Excel data in the format: 2170696969 2021965 2023241 2241710101 2018501 2050183 The goal is to past this data from a PDF file into an Access table. The data needs to end up taking the area code from the previous cell (row 1 being the exception) and create the second column: 2170696969 2170696969 2021965 2172021965 2023241 2172023241 2225691 2172225691 2241710101 2241710101 2018501 2242018501 2050183 2242050183 2052903 2242052903 I have an Excel formula to convert raw data into a specific format. Cell B1: =CONCATENATE(IF(LEN($A1)=10,LEFT($A1,3),LEFT(A...

Access Denied in Offline Mode 05-09-04
The users can use Sales for Outlook perfectly in online mode, and through the browser method. When the user clicks Go Offline, the box comes up and everything seems to synchronise fine. When actually trying to do something when in Offline Mode, it comes up with Access Denied. You can click Go Online and it seems to do that fine also, and you can continue working as normal. Does anyone have any ideas on this? I thought perhaps it was a SQL Server Authentication problem on the users local computer? Thanks! this solution worked for me TechKnowledge Error "The Logged ...

Case function
Does Excel have a function similar to the "Case" command in other programming languages? In VBA, yes Select Case myVal Case 1: 'do something Case 2: 'do something else Case Else; ' exception End Select -- HTH RP (remove nothere from the email address if mailing direct) "Al" <Al@discussions.microsoft.com> wrote in message news:F99619C8-E32C-4B61-9C81-5663E8CE879C@microsoft.com... > Does Excel have a function similar to the "Case" command in other programming > languages? XL itself doesn't, si...

right, mid, left functions??
I have a column with STATE & CITY information i.e. A1= VIRGINIA BEACH VA, A2=YAKIMA WA, A3=RENO NV, etc. I'm able to extract the STATE digits using the 'right' function; however, I can't do the same for the CITY information due to the different lenghts. Is there a formula to do this? Please help. Hi, Try this =TRIM(LEFT(A1,LEN(A1)-2)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "syl_usmc" wrote: > I have a...

Top 10 Things to Consider on Home Loans
Here are our Top 10 most important things to consider when shopping for a Home Loan, Equity Line of Credit, or Refinance, courtesy of Loans-Directory.Org: Down-Payment Fixed Versus Adjustable Rate APR Loan Types Loan Amount Qualification, Income Loan Amount Qualification, Expenses Employment and Credit History Points Sub-Prime Loans Short-Forms 1=2E Down-Payment - As a general rule of thumb, lenders will be seeking contribution from you of around 3% to 6% of the total loan value. This can be negotiable, and there are many loan packages available. 2=2E Fixed versus Adjustable - Th...

VB UML model from function
Hy How can I build an uml model from a VB 6 function? Thanx 4 any help There is no support to create a UML model using a VB6 function It can however reverse-engineer a VB project into a UML diagram -- -ronnie [MS] This posting is provided "AS IS" with no warranties, and confers no rights. "Papp Gy�rgy" <pappgyorgy@digicart.hu> wrote in message news:eEG6bJQeDHA.1716@TK2MSFTNGP10.phx.gbl... > Hy > > How can I build an uml model from a VB 6 function? > > Thanx 4 any help > > ...

Assertion Error at the end of main() function
I used the Wizard to create a "Hello World" Console 32 app. I then set the project to use MFC and am using the DAO classes. It compiles ok. It runs ok until the main() function is done executing. In the debugger, it gets to the right curly brace (}) signifying the end of main and then it gives an Assertion error in DAOCORE.CPP. I don't understand what's happening. Any ideas? Thank you. ...

problem using excel function in vba
Hi I have just been working on vba for the last 2 days only so please pardon my lack of understanding of even the very basic terms Basically, I have made an excel sheet where I have automated a process of copying a specific range of rows from sheet 1 to different sheets using for loop. everytime the loop finishes, it moves onto the next column to copy the values again. This goes on when I press the start timer button Ideally what I want to do is when I press the stop timer button, my code should automatically move onto the next column and do a calculation (variance) of al...

Need help with excel functions retrieving data
I hope someone may be able to help me out... I have a spreadsheet that is an output of all computers on our network and what applications are installed on them The spreadsheet is organized by all computer names listed in column A, and all software listed in column B. So, in column A, you will see multiple entries for each computer and in column B, you will see a different software name in each of the rows next to the computer name in column A. On another sheet, I would like to be able to choose from a dropdown list of computer names (grabbed from column A) and have it display all the softw...

how do i reference multiple rows/columns with one function?
I'm trying to reference multiple columns/rows on one worksheet to summarize in another. Can I do this with one function? Can I add a row or column on the original sheet later and have it ripple through to the other page? You can do this a number of simple ways... Each sheet can have your data in a standard format (say for regions - each sheet would have that regions numbers in it) and have a summary sheet that references all of the other sheets - if they are formated the same, it is as simple as entering the = sign in the summary cell, and then selecting the cell in each sheet you wa...