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
642 Views

Similar Articles

[PageSpeed] 13

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:

Error to eliminate a record
Hi Thanks I can't eliminate a record of quick campaign, a error say the record is in use for microsoft dynamics crm any idea? thanks for your help ...

Format issues
I have created a template in word which I call through a VB script from excel. The excel data is populated in the word every time I run this script. Everything works fine and the data get populated. I have a header in the template which is represents one of the fields in the excel sheet. The header shows the correct linked value for the first 10 pages but then on shows the variable name instead of the value. I have no idea why it does that? can someone help The code is as follows: For iCurrentRow = 1 To iLastRow If ActiveSheet.Range("B" & iCurrentRow...

How to fix Item Type Misc Charge that has negative On Hand quantit
Hello, Is there way I can fix either via GP or sql, the negative on hand quantity for item type misc? Should I change item type via sql to sales inventory and then do the inventory adjustment? Thanks for the help, MS Meg, The real question is, is this item setup as a Miscellaneous Charges item? Typically, Miscellaneous Charges, Services, and Flat Fee items do not track quantities. If the item is currently setup as Sales Inventory or other type, you will have to: 1) Consult with your accounting department and perform an adjustment to the quantity in GP to bring it to zero. 2) Upda...

Vista - Windows will not update
Keep getting this error for the last 2 weeks: 80072efe This is new, as I have been able to update in the past. I have Service Pack 2. I have turned off MalwareBytes and Avast, this didn't work. I have also tried the automatic "fix" for this, including the "aggressive" mode. Thanks in advance for your time and help. Jim H -- Jim Halpin www.dreamsports.com Dream Sports Fantasy Football When (approx. date) did you install Avast? What anti-spyware applications are installed (other than Defender)? What third-party firewall (if...

Outlook issues after Exchange migration
We recently migrated Exchange 5.5 mailboxes to Exchange 2003 on a new domain. This was performed with the exmerge utility in a 2 step process. Now there are 2 issues with Oulook: 1) When a user types a recipient name into the address field, the name autocompletes as normal. But when the mail is sent, a message returns saying it is undeliverable. However, if the user goes into the Contacts folder and from there selects the same user to send a message to, it is successful. 2) If a user shares a calendar, specifying the user with whom to share and the level of permissions, all users in ...

Why do I get a #VALUE! error with this formula???
Hi, I'm getting frustrated with this and hopefully someone can help me out. Here is my data: A B C D Hang up after accepting call AH 1 (BLANK CELL) This is the formula I'm trying to get to work, but am getting #VALUE! error =OR(IF(ISBLANK(C6),TRUE,FALSE),IF(AND(C6=1, ISBLANK(D6)),"AH",FALSE)) The desired result would be to have AH display. However if I were just to use the formula IF(AND(C6=1, ISBLANK(D6)),"AH",FALSE) it displays correctly. So I know that it has something to do with the or st...

Taskbar issue
Hello All I'm hoping someone can help me with this long-standing problem with both XP and Vista on separate laptops and every computer I've had (except I think Win98). I'm thinking there may be no solution, but what it's worth a try. With both of my machines, I have the taskbar (as well as other toolbars) set out to maximum size - typically half the screen - and to auto hide. (This makes a great program and file launcher) The issue is that the operating system decides it wants open the taskbar on its own from time to time on its own. Various things prompt this, ma...

DST issue
I entered the catalog item: <Feature Name="Item" CatalogItemId="Item:MS:sysgen_timesvc_dst" /> and confirmed that the device has the registry key: HKLM\Software\Microsoft\Clock\AutoDST = 1 But the device still does not automatically adjust for DST. I set the timezone to Pacific, set the time to 3/14/2010 at 1:59AM, waited for one minute...and the time changed to 2:00AM instead of 3:00AM. What am I missing? Thanks. which OS version are you using? have you updated it with the relevant qfes which deal with the changing in DST starting day? -...

Outlook/EX Email Issue
I'm running Windows XP with XP Professional. I cannot send email but I can recieve it using Outlook. There error says that Outlook was unable to access the SMTP server. I cannot access my email at HOME through the web email. What I can do is send/recieve email using Outlook Express AND I can access my email through my web email client outside my home. My ISP (SBC) says its not them and I'm somewhat inclined to agree with them? They say I could have a corrupted kernel32.dll file. I have turned off my Norton, deleted my account and set up a new one and re-installed XP professiona...

Dynamically building formulas
Hi I have a workbook with a number of detail sheets and one summary sheet. On the summary sheet I am using linked formulas ("Paste Special|Paste Link") to get the data, but the cell references are subject to change every time the detail sheets are updated (number of rows change, etc. This requires me to either redo the link formulas manually or to build these formulas dynamically using critical parameters (source sheet name, column, row, etc). I can successfully build the formula, but cannot seem to get it to execute (the formulas for the linked cell and the one I build dyn...

Error ox800cccoe
HI! I can receive emails; but sending emails I get an error ox800cccoe. Can you please give me a list of things to try? Many Thanks, kidsis78734 Hi, The problem you are describing can be caused if certain anti-virus software is installed on your machine. For more information on , please refer to Knowledge Base article 820670.KB.EN-US, 0x800ccc19 or 0x800cccoe Error Message When You Send E-Mail You can access this article by clicking on the link below. http://support.microsoft.com/default.aspx?scid=KB;EN-US;820670 Regards, Scott Atkins MCSE, MCSA Partner Technical Lead - Outlook Micr...

Extender Install Issues
GP 8.0 Extender install - I'm encountering the following: *Login to GP brings up screen: "Great Plains Extender needs to finish installing. This may take a few minutes". It has an OK button and clicking on this gets you into GP but it comes up everytime. How can we stop this from popping up all the time. *When creating a new Extender Window and I get select the Form, I get the following error: "A remove range operation on table 'PT_User_Defined_Window_Keys' failed accessing SQL data." Procedure or function zDP_EXT40101L_1 has too many arguments spec...

Possible Report Issue
Hi. I'm working on a database for a safety audit. The auditors would like to be able to use some of the tables and subtables as a sort of checklist for the audits. The table/subtable structure is as follows: Topic -->Subtopic -->ProgramArea -->ProgramRequirement I decided to create a report that will output that data. However, the report is only outputting information if there are ProgramRequirements already entered for a given ProgramArea. Is there a way to get it to output the ProgramArea and ProgramRequirement even if blank? Or am I going about th...

How to get a cell to error if the wrong figue is entered
I have a worksheet that is a quote form for quoting on bricks. Bricks are only avaliable in lots of 66. So if my sales people are giving a quote say for 3000 bricks this would not be possiable because they only come in lots of 66, the figue they would have to enter would be 3036. I was wondering if there is a way to get the cell to show an error if the figue entered dose not work with multiables of 66. Thanks Why not just have them enter the number of lots instead? Then have your formula multiply it by 66. ************ Anne Troy www.OfficeArticles.com "Eintsein_mc2" <simonmerk...

Adobe issues
We are running GP 9.0, using Windows 7, just installed Adobe 8.0 and are unable to send documents to PDF from GP - the PDF option remains grayed out. We have folowed the KB articles that are available, no success. Do we need to install Adobe 9.0 due to running Windows 7? Is GP 9.0 compatible with Adobe 9? Thank you for any assistance.. Hi Susan, I have a client using Adobe 9 on Windows 7. However, they are on GP 10.0 It is working succesfully. -- Geoff James "Susan" wrote: > We are running GP 9.0, using Windows 7, just installed Adobe 8.0 and are ...

Exchange 2003 Error 3018
Hi I have getting an error code 3018 (5.4.0) and just can`t resolve it. have checked my DNS and its running , have installed service pack 1 an all other updates. I am fairly new to exchange 2003 so any help would be greatl appreciated thanks A non-delivery report with a status code of 5.4.0 was generated fo recipient rfc822;hongbin@mail.san-neng.com.tw (Message-ID <ZCNQGCTOQNZBYHHKIVGB@ms17.url.com.tw>). Causes: This message indicates a DNS problem or an IP addres configuration problem Solution: Check the DNS using nslookup or dnsq. Verify the IP addres is in IPv4 literal for...

Issue with formula is Pivot table
I have the following formula in a pivot table: =IF(ISBLANK(Qtr[-1]),IF(ISNUMBER(Qtr[-2]),(Qtr[-2]*-0.08)+Qtr[-2], "),IF(ISBLANK(Qtr[-2]),0,Qtr[-1])) First to the issues that I am experiencing. First, the formula wa originally added as the one below but then updated to the formula abov to correctly reflect what I was trying to accomplish. =IF(ISBLANK(Qtr[-1]),IF(Qtr[-2]<>0,(Qtr[-2]*-0.08)+Qtr[-2], "),IF(ISBLANK(Qtr[-2]),0,Qtr[-1])) This issue is the original formula appears in the very first cell o the pivot table and then periodically through out the column. So, yo see ...

Outlook Web Access OWA Issue - Login Prompt for Images
Dear Outlook 2003 / Exchange 2003 Users, Starting over the last two weeks (possibly after an update on the Exchange server), all of my users are starting to encounter the same issue when using Outlook Web Access (using IE6 on XP Pro systems). We have images in our signature block, and now when someone replies using Webmail to an e-mail originally sent via Outlook 2003, a login prompt appears for each image in the e-mail. Every time you cancel the login box, a red x image box appears, until all of the images that should be there are represented by the red x boxes. For the record, this has w...

Hyperlink Issue #2
I have an issue with hyperlinks. When you click on the hyperlink on a webpage it returns an error stating that the the "item could not be created". Outlook 2000/Windows 2000 ...

No data return from a sub-query causes an error message
I have a sub-query which counts the number of referrals received [Date Received]. The user then enters the criteria of the time period between [start date] and [end date] and the locality [Town]. There are 9 different localities. When the query is run and there have been no referrals in a particular locality, the table appears as column headings with no data. The query that this links into (as do several other sub-queries) then also returns no data. How can I get the first sub-query to return data for each of the different localities, with 0 if there have been no referrals? If someone ca...

Update Issues
Has anyone experienced any problems with XP Pro updates sent in Feb.? My Lenovo T61p has performed flawlessly for 2+years but suddenly started turning off and now stays on but freezes-up. I can’t detect a virus and seems like the Feb. updates are all that has changed. I plan to uninstall them….between freeze-ups, unless someone has a better thought. Thanks >Has anyone experienced any problems with XP Pro updates sent in Feb.? My >Lenovo T61p has performed flawlessly for 2+years but suddenly started turning >off and now stays on but freezes-up. I can�t detect a ...

ToolBar error when enter Dialog 2nd time.
Hi; I've created a dialog in an aplication with VC6 which has a Toolbar created in the onInitDialog as follows: ////////////////////////////////////////////////////// if (!m_editorTB.CreateEx(this, TBSTYLE_FLAT, WS_CHILD | WS_VISIBLE | CBRS_TOP | CBRS_GRIPPER | CBRS_TOOLTIPS | CBRS_FLYBY | CBRS_SIZE_DYNAMIC | CBRS_BORDER_BOTTOM | CBRS_BORDER_RIGHT) || !m_editorTB.LoadToolBar(IDR_EDITORTB)) { TRACE("Problem creating toolbar\n"); } RepositionBars(AFX_IDW_CONTROLBAR_FIRST, AFX_IDW_CONTROLBAR_LAST,0); //////////////////////////////////////////////////// When I enter th...

~ issue
I am currently using Outlook Express 6 and have noticed that for the second time (did a format the first time thinking I had a virus!) that when I have downloaded the 330994 April 2003, Security update for Outlook Express 6 SP1 that anytime I make a change to my address book and reboot I get an icon on my desktop that is named "~" and it basically is my entire address book. Has anyone else experienced this problem and how do I remedy it? Thank you for any help! Sue In reviewing the Outlook Express newsgroups, this is reported as a known issue w/ no fix from Microsoft (report...

Multiple Outlook Mailboxes issues with 3.0
I have a large number of users who have more than one outlook mailboxes, When I open an email from one of the addtional mailboxes, I get an error message that tells me "Only Items in the default Outlook store can be promoted to Microsoft CRM." Does anyone know if this can be worked around?? ...

chkdsk issue
Whenever I run chkdsk /r, it finds quite a few errors and corrects them. An example is a lot of orphaned files. Then, if I run it again right away, it does the same thing and finds the sa,e orphaned files. What causes this and how can I correct it? Thank you. On 14 May 2010, five256@NOwhere.com wrote in microsoft.public.windows.vista.general: > Whenever I run chkdsk /r, it finds quite a few errors and corrects > them. An example is a lot of orphaned files. Then, if I run it > again right away, it does the same thing and finds the sa,e > orphaned files. What causes...