+ Reply to Thread
Results 1 to 8 of 8

Cant get a forumula to work only if a condition is met.

  1. #1
    Registered User
    Join Date
    07-05-2008
    Location
    Camp Pendleton
    Posts
    20

    Cant get a forumula to work only if a condition is met.

    I am a novice with excel so I really hope this makes sense to those of you who are experts.

    The following code is placed in box I3

    #=IF H3 ><0.00 then =SUM(L2)-(H3)#
    (if H3 is any interger negative or positive then perform calculation)
    (If H3 false then I3 "remains blank" with no words or numbers in it)
    Excel has been telling me this wont work.

    I will start from the begining:

    Box L2 has a sum from another worksheet in it.
    I want to have box I3 display the sum of boxes L2 and H3.
    I only want it to display the result if there is an interger put into box H3.
    If there is nothing put into box H3, I would like I3 do remain blank.
    ________________________________________________________

    In other words:
    I4 should only display the sum of I3 and H4 if and only if a value has been typed into H4.
    or
    I5 should only display the sum of I4 and H5 if and only if a value has been typed into H4

    Subsequently:
    I would like box L3 to choose the value of the last box in column I with a SUM in it to display, even if that value is $0.00 But that wont work correctly if 0.00 or any other number goes on to infinity.

    Further:
    I3 = $6.00
    H4 = $-5.00
    I4 = $1.00
    H5 = Blank
    I5 = Blank (because an interger has not been typed into H5)
    L3 = $1.00 (by choosing I4 because I5 was blank)


    Specifically the problem I am having is, if I #=SUM(L2)+(H3) and copy that code to all the boxes in column I then I get the sum of the last calculation to infinity. Or I can make the values $0.00 to infinity but then L3 will not choose correctly.

    Any help with this would be great! I have been messing with =If and =SUMIF as well as a few other trials and I cant get anything to work.
    Last edited by blizzard0c; 07-06-2008 at 06:22 PM. Reason: Added Workbook Example

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,598
    #=IF H3 ><0.00 then =SUM(L2)-(H3)#
    =if (h3<>0,l2-h3,"")
    what's with the sum part??

  3. #3
    Registered User
    Join Date
    07-05-2008
    Location
    Camp Pendleton
    Posts
    20

    Smile Thanks for the help!

    I got it to work this way

    =IF(H3<>0,(L2)+(H3),"")

    and

    =IF(H3<>0,(L2)-(H3),"")

    Now I'll mess with L3 for a while and try and get it to choose the correct box from column I to display.

    Thanks Again!
    Last edited by blizzard0c; 07-06-2008 at 04:56 PM.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,598
    Quote Originally Posted by blizzard0c
    I got it to work this way

    =IF(H3<>0,(L2)+(H3),"")

    and

    =IF(H3<>0,(L2)-(H3),"")

    Now I'll mess with L3 for a while and try and get it to choose the correct box from column I to display.

    Thanks Again!
    This will get the last value in column A
    =INDEX(A:A,MATCH(9.99999999999999E+307,A:A))
    change column letters to suite

  5. #5
    Registered User
    Join Date
    07-05-2008
    Location
    Camp Pendleton
    Posts
    20

    Thanks Again!

    That works great!

    How do I add Column L (more specificaly box L2) to

    =INDEX(I:I,MATCH(9.99999999999999E+307,I:I))

    So L3 will display the value in L2 when there are no values in Column I

    As is, when there are no values in column I L3 displays #N/A

    I was trying
    =INDEX(I:I,L2MATCH(9.99999999999999E+307,I:I,L2))

    but then it gives me the #name error
    Last edited by blizzard0c; 07-06-2008 at 05:37 PM.

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,598
    Quote Originally Posted by blizzard0c
    That works great!

    How do I add Column L (more specificaly box L2) to

    =INDEX(I:I,MATCH(9.99999999999999E+307,I:I))

    So L3 will display the value in L2 when there are no values in Column I

    As is, when there are no values in column I L3 displays #N/A

    I was trying
    =INDEX(I:I,L2MATCH(9.99999999999999E+307,I:I,L2))

    but then it gives me the #name error
    Attach a sample workbook for quicker results..........

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Try

    =IF(COUNT(I:I),LOOKUP(9.99999999999999E+307,I:I),L2)

  8. #8
    Registered User
    Join Date
    07-05-2008
    Location
    Camp Pendleton
    Posts
    20

    Thank you very much!

    Quote Originally Posted by daddylonglegs
    Try

    =IF(COUNT(I:I),LOOKUP(9.99999999999999E+307,I:I),L2)

    And there it is!

    Thank you guys so much for sharing your knowledge. I have been working on this for 3.5 days now and finally its done. I now have a "living" financial statement with an Income and Balance Sheet as well as an "activity regester" which I can easily transfer for use on my cell phone. This will especially save me time while on business trips and I no longer have to carry around a bank regester. This is great!

    Thank You!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1