Issue writing IF OR Error Formula Types

Having trouble putting a formala together.

I have the following formula which works:

Senario : $d$d = either weight or unit or unknown (of an ingredient)
e8= is  a quantity number ( either in grams or units, o
an ingredient. eg 100 or 1)
Raw data is in nutrition!$b$6:$g$70

The following formula works so far:

f8 ( which is the no. o
calories)=if($d$d="weight",e8/100,e8)*vlookup(c8,nutrition!$b$6:$g$70,3,false)

But I also want to add to it that

If e8(quantity)=0 or blank or invalid then "qty?" should appear in th
f8 cell

And not sure what to do to avoid an error in f8 if $d$d = unknown??

Am completely stuck here, need help asap.

lozza


--
lozzam
0
10/26/2006 4:11:21 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
879 Views

Similar Articles

[PageSpeed] 52

Hazarding a wild guess ..
Perhaps you mean something like this:
=IF(E8="","qty?",IF(D8="weight",E8/100,E8*VLOOKUP(C8,nutrition!$B$6:$G$70,3,0)))
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"lozzam" <lozzam.2g9vgp@news.excelbanter.com> wrote in message 
news:lozzam.2g9vgp@news.excelbanter.com...
>
> Having trouble putting a formala together.
>
> I have the following formula which works:
>
> Senario : $d$d = either weight or unit or unknown (of an ingredient)
> e8= is  a quantity number ( either in grams or units, of
> an ingredient. eg 100 or 1)
> Raw data is in nutrition!$b$6:$g$70
>
> The following formula works so far:
>
> f8 ( which is the no. of
> calories)=if($d$d="weight",e8/100,e8)*vlookup(c8,nutrition!$b$6:$g$70,3,false)
>
> But I also want to add to it that
>
> If e8(quantity)=0 or blank or invalid then "qty?" should appear in the
> f8 cell
>
> And not sure what to do to avoid an error in f8 if $d$d = unknown??
>
> Am completely stuck here, need help asap.
>
> lozzam
>
>
>
>
> -- 
> lozzam 


0
demechanik (4694)
10/26/2006 7:14:05 AM
Reply:

Similar Artilces:

GP 10 WorkFlow Error 05-27-10
Hi I am receiving an error in GP 10 WorkFlow. At the end the workflow fails with the error. "An error has occurred in the Approval Status". Would be very helpful if any lead could be provided. I googled around but no solution was specified to this error. Kindly Advise. Following is the Content of the Event Log Action: SubmitForApproval Current User LogOnName: FZE\Administrator Input parameters: <RequestObjects> <ArrayOfBusinessObjectSubmissionInformation xmlns:xsi="http:// www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www...

Error Message #31
One of my spreadsheets will not open. I get an error message that says: Program error. what causes this and can I retrieve my spreadsheet? But other workbooks will open ok? If yes, the first thing I'd do is try to find a voluteer that could try to open it on their pc. If that didn't work, sometimes you can recover your file by opening in saving in a later version of excel. Other times, openoffice can open files that excel can't. http://www.openoffice.org, a 60-65 meg download or a CD Firefli wrote: > > One of my spreadsheets will not open. I get an error message that...

error in importing data
Sorry the error is 0x8004032d ...

Outlook 2003 -- miscellaneous issues, mainly with Offline Folders
Outlook 2003 seems to really dislike Offline folder synchronization if a user receives any virus infected emails (ie. if something has NetSky in it) -- is there a way to prevent the synchs from erroring? They use NAV for Exchange and it should be protecting them so I don't think that's a concern. ...

Runtime Error 2001 you canceled the previous operation
Dear all Eperts I have write VBA code like this Private Sub calenstart_Click() Me.Filter = "startdate='" & calenstart.Value & "'" Me.FilterOn = True End Sub when i click on my calendar it show message error Runtime Error 2001 you canceled the previous operation I can't fix it, what something wrong. Help me Thanks The message means that the filter string is not valid. Consequently, the next operation (applying the filter) fails. Presumably startdate is the name of a date/time field. Here are some reasons why it could fail: a) you used ' inste...

Issue with Deployment Manager
Dear Friends, After install CRM 1.2, when we try to access Users Administrators in the Deployment Manager, we receive a message, that we can't connect to METABASE Database, errocode 80004005 Has somebody find the same problem? Thank you. Manel Moreno Is crm from the web client working? "Manel Moreno" <mmoreno@watermark-europe.com> wrote in message news:u5OzbYo5DHA.1040@TK2MSFTNGP10.phx.gbl... > Dear Friends, > > After install CRM 1.2, when we try to access Users Administrators in the > Deployment Manager, we receive a message, that we can't connect t...

Exchange 2003 SP1 Install Error
When I try to install E2003 SP1 I get the error: EventType exchangesetuperror, P1 6_5_7226_0, P2 update, P3 microsoft exchange messaging and collaboration services, P4 install, P5 e0070101, P6 _null_, P7 1, P8 NIL, P9 NIL, P10 NI The log says it cannot find a file but I can't tell which file it's looking for. Any ideas how to fix this? Thanks ...

Formulas not updating ?
I have a problem with my spreadsheet under Excel XP. The problem has to do with cell references not updating when a row is inserted into the worksheet. The formulas in the cells are relatively simple: =B13 and =DATE(YEAR(B10),MONTH(B10),DAY(B10)+7) I can copy a row and insert it elsewhere or just insert a blank row and the cell references in rows below are not updated in all cases. It seems that rows that are separated from the inserted rows by a blank row are updated. Is this a known issue or does anyone have any suggestions on how to fix this? The spreadsheet is intended to p...

error 2147217865
I am experiencing an Error message on my second station when trying to post a transaction, usually the first or second after I open Store Operation Pos. My main data is sitting on first station. This is the error msg in Full. Error 214721865 Cannot Drop the Table # Temp Because it does not exist in System Cat Log (Source: Microsoft OLE DB Provider for SQL Server) (SQL State:40502 Native Error 3701) Select GetDate ( ) As Current Date Time I am unable to find this error on customer source Can you post cause and RESOLUTION ---------------- This post is a suggestion for Microsoft, and Micros...

Script error in IE8
When I am trying to use the general search function in Win XP sp3 (all recent updates) from the start menu, I get the following error message: An error has occurred in the script on this page Line: 38 Char: 5 Error: permission denied Code: 0 URL: http://ie.search.msn.com/sa/5_12_0_512943/srchasst.js Do you want to continue running scripts on this page? Yes or No What could I do to correct this? "jiger" <jiger@discussions.microsoft.com> wrote in message news:54E2513A-6273-41CA-A429-46C1F995B995@microsoft.com... > [Subject: Script error in IE8] ...

Locking Formulas to Cells
I don't know if I am using the correct terminology but this is what want to do: I have placed formulas in multiple columns that calculat my sales numbers for a bid. The problem I am running into is that change the bids for every person and when I clear a cell is clears th formula from it as well. Is there a way for me to clear cells withou deleting the formula I have placed inside it? I know that I can jus grab the first cell in the column and drag it down to re-load th formula in that column but I don't want to have to do that. I want th formulas permenant and the data I enter...

How do I type an 'overline' or 'overbar'?
For example, in writing mathematical notation, to designate an average figure, one places a horizontal bar over the variable name. The same notation is used in linguistics to indicate a phrase at a particular level of analysis. I have need of both of these, but how do I get Word to do it? Thanks for your help, Peyton Todd See http://sbarnhill.mvps.org/WordFAQs/Overbar.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Peyton Todd" <PeytonTodd@discussions.microsoft.com> wrote in message news:918...

Office assistant error...
Hi, Having an issue on a standardised platform of XP with Office 2000 (SR2) and Outlook 2003. Users experiencing an error when starting Office Assistant - "The office assistant could not be started." The recommendation is to repair the office installation. Have attempted this as well as a reinstallation of Office 2000 and Outlook 2003, all to no avail. The error is sporadic but is occuring often enough to warrant this post! Anybody out there got some suggestions? Cheers, Darren Kukulka Systems Manager Beacon Technology ...

Changing Titles by a formula
From cell A1:A10 I have standard titles that are linked to another sheet. I only need to change the office and date per each title. Is thier a way to do that making the office and date a formula in another cell? example of title ABC Comp, Office 38L Sales as of 6/07/2004 I need to only keep changeing the 38L and date. Any suggestions----Thanks Say Office # is in Column 'C' in cell A1: ="ABC Comp, Office "&C1&" Sales as of "&Today() ----- Heather wrote: ----- From cell A1:A10 I have standard titles that are linked to ...

how can I drag formulas with other cell references
example cell A1 contains formula "=stdev(a2:a7) cell a2 contains formula "=stdev(a8:a13) Is it possible to drag the formula down so that cell a3 contains "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, a6, a7 etc. etc. Hi Bram, See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) It was easier to test and verify using SUM instead of stdev, but the formula is same NOTE the formulas are in a different colu...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

error messages
Every time I use my email, I get an error message that I need to close and send report to microsoft. I have tried the process of eliminationg add-ons and remove third party addons . I have also ran the complete scan to locate the problem but the problem still exists. "Cottontop" <Cottontop@discussions.microsoft.com> wrote in message news:FE8C3F3F-FD22-4D34-9A8E-48C23567FFAD@microsoft.com... > Every time I use my email, I get an error message that I need to close and > send report to microsoft. I have tried the process of eliminationg > add-ons >...

Formulas don't work in certain cells #2
nope, the cells are formatted as numbers. I simply cannot figure thi out. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27423 Being formatted as numbers doesn't automatically mean they are numbers. Copy an empty cell formatted to General. Select your range of numbers and Paste Special>Add>OK>Esc Gord Dibben Excel MVP On Mon, 1 Nov 2004 11:59:46 -0600, kalik247 <kalik2...

Contract
When I attempt to Invoice a Contract, I get the following error: <description>The service address of the contract is invalid.</description><details>The service address is invalid, this contract can not be set to invoice state.</details> Does anyone know what the issue/error could be? Hi Kathy, Not sure about service address, but check if the contact address and bill-to address for the contract has been set. To invoice a contract both these addresses are required. Hope this helps! Sam _______________ Inogic Innovative Logic Innovative solutions for your SME E...

Error in Detailed Sales Report
Dear All, Have you encountered a strange scenario wherein in your shop you have sold 1 item in that particular invoice/receipt # but when it got connected and process the WS401 and when you run the detailed report in HQ level , it was added up with some items which is not in the original shop transactions. Example.. Store A Trx # item Qty 1023 apple 2 in HQ Store Trx# Item Qty A 1023 apple 2 A 1023 orange 3 A 1023 banana 2 Which in the store A transaction, those items where not in that trasaction but somehow when it was connected and process...

Backup error Access Denied
I have been recieving the following error message for a while and can't figure out how to fix it. It is only happening on one mailbox. "Backup - \\MBMAIL\Microsoft Exchange Mailboxes Access denied to file Jhon Doe [jdoe]Top of Information StoreSync IssuesServer FailuresMail Delivery (failure jdoe@xyz.com)." Help Please Can you access the mailbox from a mail client? If so, you can use mdbvu to look at the Top of the Information store folder, and delete the message in there. If you cannot access the mailbox, then I have seen it where some users think they are ...

Installation of KB970430 Failed, Error 800B0100
Tried to install KB970430 automatically and manually and failed. Received error 800B0100 which took me to System Update Readiness Tool and in to an apparent circle. Using Windows Vista. jtm wrote: > Tried to install KB970430 automatically and manually and failed. Received > error 800B0100 which took me to System Update Readiness Tool and in to an > apparent circle. Using Windows Vista. Suggest you clean boot Vista and then try to run the System Update Readiness Tool (CheckSUR) again: How to troubleshoot a problem by performing a clean boot in Windows Vista or in...

"cannot empyt clipboard" error
I am getting the cannot empty clipboard error after every cut/paste or drag/ drop. I'm running Windows XP & Excel 2003. Is there anything to fix this? ...

An error occurred while trying to promote the e-mail. Try again
I have an email that I sent using Outlook (didn't use the CRM Email option). I now want to promote the email into CRM so that we have a permanent record of the email associated wioth the contact. I open my sent items, select the email and click the 'Promote E-Mail to CRM Activity' button in SFO. I get the error "An error occurred while trying to promote the e-mail. Try again" No matter how many times I 'try again' I get the same message. Can anyone tell me what I'm doing wrong and how I can get the email into CRM? Cheers Graeme SFO doesn't have any...

sorting formulas
Hi, Ive made a spreadsheet which all the cells accept titles contain look up formula's. I want to be able to do sorts on the columns but it doesnt seem to work. I think its because excel recognises the content of the cells as a formula, but i want it just to see the result of the look up and sort that. Is there anyway to solve this problem? Thanks to all who help. Matt -- m4tt ------------------------------------------------------------------------ m4tt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25835 View this thread: http://www.excelforum.com/s...