#### Trying to get this if statment to work

I have excel 2000. I am trying to get this formula to work. It does not give
me any errors or anything It just will not return the number in the cell that
it references

Cell CJ44 has the formula

Here is an example of the cells

CJ44 formula CL44 has =IF((I44=0),0,INT((I44-10)/2)) (the value in their is 0)
CK44 has =IF((H44=0),0,INT((H44-10)/2)) (the value in their is -1) CP44 (the
value in their is Y) but I want it to fuction normally if their is nothing
(blank) in cell CP44

So this is what I have written, but the value does not return to what I want
it to be.

=IF(H44=0,CL44,IF(I44>0,CK44,IF(CP44=Y,CL44)))

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201006/1

 0
Cerealkiller
6/5/2010 6:29:43 PM
excel.worksheet.functions 4936 articles. 2 followers.

2 Replies
1612 Views

Similar Articles

[PageSpeed] 29

Firstly if Y is a value in cell then it needs to be enclosed in double
quotes in the formula.

Anyway do I interpret your question correctly that you want to test for
either "Y" or Blank Cell at CP44. If so, then try the following.

=IF(H44=0,CL44,IF(I44>0,CK44,IF(OR(CP44="Y",ISBLANK(CP44)),CL44)))

I think that ISBLANK function is available in xl2000 but just in case it is
not then could use the following.

=IF(H44=0,CL44,IF(I44>0,CK44,IF(OR(CP44="Y",LEN(CP44)=0),CL44)))

Both formulas will return FALSE if it gets to the last IF statement and CP44
is not blank and has a value other than "Y".

--
Regards,

OssieMac

"Cerealkiller via OfficeKB.com" wrote:

> I have excel 2000. I am trying to get this formula to work. It does not give
> me any errors or anything It just will not return the number in the cell that
> it references
>
> Cell CJ44 has the formula
>
> Here is an example of the cells
>
> CJ44 formula CL44 has =IF((I44=0),0,INT((I44-10)/2)) (the value in their is 0)
> CK44 has =IF((H44=0),0,INT((H44-10)/2)) (the value in their is -1) CP44 (the
> value in their is Y) but I want it to fuction normally if their is nothing
> (blank) in cell CP44
>
> So this is what I have written, but the value does not return to what I want
> it to be.
>
> =IF(H44=0,CL44,IF(I44>0,CK44,IF(CP44=Y,CL44)))
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201006/1
>
> .
>
 0
Utf
6/6/2010 7:41:13 AM
OssieMac wrote:
>Firstly if Y is a value in cell then it needs to be enclosed in double
>quotes in the formula.
>
>Anyway do I interpret your question correctly that you want to test for
>either "Y" or Blank Cell at CP44. If so, then try the following.
>
>=IF(H44=0,CL44,IF(I44>0,CK44,IF(OR(CP44="Y",ISBLANK(CP44)),CL44)))
>
>I think that ISBLANK function is available in xl2000 but just in case it is
>not then could use the following.
>
>=IF(H44=0,CL44,IF(I44>0,CK44,IF(OR(CP44="Y",LEN(CP44)=0),CL44)))
>
>Both formulas will return FALSE if it gets to the last IF statement and CP44
>is not blank and has a value other than "Y".
>
>> I have excel 2000. I am trying to get this formula to work. It does not give
>> me any errors or anything It just will not return the number in the cell that
>[quoted text clipped - 13 lines]
>>
>> =IF(H44=0,CL44,IF(I44>0,CK44,IF(CP44=Y,CL44)))

Thank you for your help I will try it out and see if it works

--
Message posted via http://www.officekb.com

 0
Cerealkiller
6/6/2010 5:13:07 PM

Similar Artilces:

Mail gets delivered, but never shows up
Hey everyone, I was wondering if anyone has experienced these symptoms before: I can send email through my exchange server (SBS 2003) just fine. Recieving mail is accepted by the SMTP component, and delivered to the appropriate mailbox according to the Message Tracking Center. However, no new mail since last week is showing up in Outlook! To diagnose this, I tried logging in via OWA instead of the full outlook client, only to find that I see the new messages' subject lines, but when I click on a particular subject to load the full message, I get a "HTTP/1.1 500 Internal Serve...

Want to get rid of UTC offset in serialization of DateTime
Hi everyone! In the following code we get a UTC offset in xml. Since I want my WAP users in UTC+02:00 to see data on a server in some US time zone, a lot of confusion will be created from this behavior. Either I use .NET logic on the server and get server local time or I create xhtml pages directly from serialized objects. Eitherway I'll have to write my own logic to take care of utc offset. Is it possible to override XmlSerializer so what my local desktop user send to the webservice is saved on the server without utc offset? That way, the time that is local to my users will be saved on ...

trying this out
This is a test posting. On Tue, 13 Nov 2007 16:06:51 +0800, "hmmmm" <bobo123@dotdot.dyndns.org> wrote: >This is a test posting. > Please use microsoft.public.test or microsoft.public.test.here for your testing. This is a working newsgroup. If you have questions about an Access database, please post them. John W. Vinson [MVP] ...

I am trying to round up to the nearest 25 cents
I am trying to round a money value to the nearsest 25 cents and it keeps rounding to the nearest dollar HELP Try =CEILING(A1, 0.25) In article <2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com>, sypher <sypher@discussions.microsoft.com> wrote: > I am trying to round a money value to the nearsest 25 cents and it keeps > rounding to the nearest dollar HELP =ROUND(A1*4,0)/4 -- HTH Bob Phillips "sypher" <sypher@discussions.microsoft.com> wrote in message news:2DB8F007-1E27-4A48-923B-C7D4D0238B5E@microsoft.com... > I am trying to round a mone...

Notify Alert does not work in Excel
In a network environment, opening an Excel workbook that is already opened by someone else, prompts me with a choice of "Open as Read-Only, or choose Notify to be alerted when it is available". Although I choose "Notify", I do not receive any notification when the file is closed. Is there a setting within MS Office that needs to be configured for this feature to work? ...

Trying to clear up Received but not invoiced report
I am trying to clean up the received not invoiced items. The problem began with entering a purchase order and then not matching the invoice to the items received agaisnt the purchase order. SO we thought that doing a returned transaction entery would solve the problem. The only thing is now the return shows up on our Received/ Not invoiced report. And the report is becoming a huge mess and difficult to use. What is the best way to clear up items that have been received but were not matched and already paid through the invoice entery? There is a knowledgebase article on this very thing av...

How can I get toolbar buttons back?
I accidently removed the buttons on the toolbar. It was the icons for home, page, options (I think) and some others. I was tryimg to customize and clicked on something that made those disappear. How can I get them back? -- my_realtor (Linda) Right click on the Toolbar | Customize | Add or Remove Commands and add them back. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "my_realtor" <Linda@discussions.microsoft.com> wrote in message news:3726FE3F-F5B8-4EF8-868B-009799DB8A8C@microsoft.com... >I accidently removed the button...

New computer. Microsoft XP Home. Microsoft Office XP Small Business 2002. Earthlink dial-up account. Two e- mail accounts. After opening Outlook, 1st send/receive (option 3, all accounts) usually prompts for both passwords, downloads messages, all good. Next time, though, no prompt for account 2, error cannot connect to server, account 1 usually gets messages. Every five minute send/receive option eventually degenerates to cannot connect to server errors for both accounts. Meanwhile internet browsing slows to a crawl. Any ideas? ...

user doesn't get internet mail
hi, i exch 5.5 and a user that doesn't get mail from the internet. he can get from inside and send with no problem. thank you ron On Mon, 22 Nov 2004 03:19:04 -0800, "iz" <iz@discussions.microsoft.com> wrote: >hi, >i exch 5.5 and a user that doesn't get mail from the internet. >he can get from inside and send with no problem. >thank you >ron Has he actually got an smtp address on his mailbox properties? Internal mail in 5.5 doesn't use the smtp ...

Getting Exchange Protocol Virtual Server Status (Start,Stop,Paused)
Hi All, We can see Virtual servers for every protocols on 'Exchange System Manager'. By right clicking the each virtual severs we can see the status as (Start, stop or Paused). But I like to get status information programmatically. Can any one can provide a snap or code or any link that will help me to get the above information. Thanks in Advance Manu I don't know but I would think this would be exposed through CDOEXM. PaulB "Manu" <dinil_kv@hotmail.com> wrote in message news:OohMS9zvFHA.2956@TK2MSFTNGP09.phx.gbl... > Hi All, > > > > We ca...

How do I cancel a msg Outlook keeps trying to send unsuccessfully?
How do I cancel an "in process" email Outlook keeps trying to send unsuccessfully due to a huge email message I tried to send with way too many attachments? It's stuck in "send" mode, and as a result, I can't send any emails because Outlook cannot get past trying to send this message. How do I cancel a pending outbox transmission while it's trying to send? I've tried everything I can think of to no avail. Please help! Angelczech wrote on Fri, 12 March 2010 07:32 > How do I cancel an "in process" email Outlook keeps trying to send ...

Getting Tab Text
Hi, I have a Tab Control with Tabs. I'm trying to get the Tab Text. I can get the tab and set the text no problem. But, when I try to read the text, pszText is alway's a <Bad Ptr> If I immediately get the Item after I set the Item, It shows the Text. //Initially setting Tab Text. TCITEM tcItem; tcItem.mask = TCIF_TEXT; tcItem.pszText = String.GetBuffer(); String.ReleaseBuffer(); //I don't think this matters. It fails both with and without. c_TabCtrl.InsertItem(c_TabCtrl.GetItemCount(), &tcItem); //Trying to get Tab Text TCITEM tcItem; tcItem.mask = TCI...

How to get add-in functions to show up in function list?
I managed to create an add-in (test.xlam) and get in installed. It has one function: Function myarea(length As Variant, width As Variant) myarea = length * width End Function If I enter "=myarea(3,4)" in a cell, I get 12. What I don't get is prompted with the name of the function. If I type "=m", I am offered choices from Match to Multinomial, but not MyArea. Can I get my add-in functions listed and then, once selected, promted for the arguments? Which version of Excel are you using? In Excel 2007 you get user defined functiona listed as well as the bui...

Get 'Requesting data...' message
I moved my computer to a totally different domain. Ever since then I periodically get a screen that pops up while in Outlook that says 'Requesting data from server E01...' E01 is the Exchange server on the OLD domain. I am not connected to the domain on which this server resides. I've looked everywhere in my Outlook setup to try to determine why it's looking for E01 and I can't find it anywhere. I believe this is also causing some latency problems; for example, after composing an e-mail and clicking on Send, it can sometimes take 10-15 seconds before the e-ma...

Display one number in a cell even though 2 numbers get pasted into the same cell?
I am trying to figure out how to format a cell so that it only display the first number, even though two numbers are posted in the cell. The reason that two numbers are posted in the cell is that I am copyin the data from a website and when I post it into excel, it is using HTM format to paste... For example, if I post a column, it will have numbers set up lik this: +3 -109 -3 -103 +11 -110 -10 -105 The +3 -109 would all be in the same cell. I need each one of thos cells to just display +3, then -3, then +11, then -10 after I past them into the worksheet. I want to take just the first...

archive date not working
When I try to archive my money file, it does not use the date option. I asked it to archive, selected a date of 1/1/00 (and also 12/31/99). When the archive was created, it had everything from 1995 through today, 2003. I just purchased Money 2004 Deluxe. Have never used archive before. Suggest you forget all about archive - it is an absolute pain in the butt and causes more problems than it theoretically solves. Many of us have been very happily running for 8 years or more without ever archiving. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.c...

How to get ID of fileupload control
Hi All, I am having problem to get the ID of fileupload control in as loop: <form enctype="multipart/form-data" method="post" runat="server"> <asp:FileUpLoad id="FileName1" runat="server" /> <asp:FileUpLoad id="FileName2" runat="server" /> <asp:FileUpLoad id="FileName3" runat="server" /> </form> Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Page.IsPostBack Then Dim i As Integer For i = 0 to Page.Contro...

Outlook news group stoped working
I am using MS Outlook 2007 on a DELL XPS / Vista ultimate mashine 20 min ago newsgroups stoped working and I get this message: 502 Access denied (Hit connection limit) (newsfe17.iad) (Tornado vi.2.5.548) Any idea what this means?? "Ted" <vitanov@shaw.ca> wrote in message news:Mm3sl.8\$5t4.6@newsfe24.iad... >I am using MS Outlook 2007 on a DELL XPS / Vista ultimate mashine > 20 min ago newsgroups stoped working and I get this message: > > 502 Access denied (Hit connection limit) (newsfe17.iad) > (Tornado vi.2.5.548) > > Any idea what this means?? Sound...

trying to insert a 0 in front of zipcode
Hello, I do not use excel at all so i have no clue! I have a list of daycares that i want to create mailing labels with...about 700 of them but the problem is my zipcode column there needs to be a zero in front of all 700 zipcodes how to i insert a zero in front of them without having to type it into each box? the other question is can i print labels directly from excel or do i have to go thru ms word? thanks Greg One of these 1) format the cells with custom format 00000 2) format the cells with Special / Zip Codes 3) format the cells as Text 4) begin all zip codes with an sin...

How do I get my send button to show
I can create a new message but can not send it, as I do not have a send button on screen. I went to help and it suggested maybe I do not have an account so I tried to set one up but do not have the right connection in the tool area. Can someone please help. I use MS Office Outlook 2003. Send button will show up after you correctly set up your account in Tools, email accounts. Get your POP server settings from your Internet provider. Outlook is not used for free Web mail like Yahoo or Hotmail. "maddie" wrote: > I can create a new message but can not send it, as I do not ...

error message when trying to install WMP 10 or 11
My operating system is Windows XP Home Edition 2002. I'm trying to download either WMP 10 or 11 and I get a message saying it was not possible to complete setup, see webhelp for more assistance, however there is no additional information when I click Web help. the Error message is 8007F0DA. Can anyone help me? Thanks. On Tue, 30 Mar 2010 07:54:01 -0700, Nate <Nate@discussions.microsoft.com> wrote: > >My operating system is Windows XP Home Edition 2002. I'm trying to download >either WMP 10 or 11 and I get a message saying it was not possible to &g...

Try or TRY
Hello, Will there be any opitmization or ... when we use INT or TRY macros instead of int or try ? Which one is better to be used? Thanks, JSmith "JSmith" <jsmithmitra@yahoo.com> wrote in message news:%23G1sOVdoEHA.692@TK2MSFTNGP12.phx.gbl... > > Hello, > > Will there be any opitmization or ... when we use INT or TRY macros instead > of int or try ? Which one is better to be used? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/html/_core_exceptions.3a_.converting_from_mfc_exception_macros.asp -- Jeff Partch [VC++ MVP] FYI, TRY ...

error message when trying to delete messages in inbox
I cannot delete messages in my email inbox. When attempting to delete messages, Outlook says "The messaging interface has returned an unknown error. If the problem persists, restart Outlook. I continue to get the message even though I restarted Outlook. My mail works fine, but I just cannot delete anything. Does someone know a solution? Can you delete them if you hold shift as you delete? How many items are in the deleted items folder? Are you using any type of AV scanner on the message store? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coa...

Problem trying to export e-mail messages
When I go into Windows Mail and export my messages, the export completes but then I go and the folders don't match my existing e-mail folders. There are folders showing which I had deleted months ago. Could it be a corrupt file? How can I just transfer the e-mail messages I need to a new PC with Windows 7? Thanks> Windows Mail uses internal mail folder names that may not correspond exactly to the folder name in the user interface. What are you doing with your Windows Mail messages in Windows 7? Windows Mail was not designed to run on Windows 7. --=20 Gary VanderMole...

Sync has stopped working
For several months, I have been able to sync Entourage with my Palm m130. Just last week, the conduit started crashing. I have figured out that it happens when syncing my calendar. All other parts of Entourage sync just fine. Has anyone else had this problem? Any help would be greatly appreciated. Please e-mail me at greg.mcbride@e-commedia.com if you have any suggestions. Thanks, Greg McBride Make sure all of your recurring events have end-dates attached. Events without end dates can cause crashes during the synchronization process. An easy way to find them is to go to Edit -> Advance...