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

1. ## 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.

2. #=IF H3 ><0.00 then =SUM(L2)-(H3)#
=if (h3<>0,l2-h3,"")
what's with the sum part??

3. ## 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!

4. 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. ## 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

6. 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. Try

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

8. ## Thank you very much!

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!

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

#### 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