Help with complex Microsoft Excel formula?

Question

If someone can help me get this to work I'll be able to know how much money I'll have on me on any day of the year. I'm putting all my bills for the year on the Bills sheet, and I'll list their amounts by their due dates (or how much and when I intend to pay) on a dated column on the current sheet (called Money). And I'll contrast that by the money I'll have incoming. It will be perfect with this formula. Thanks!

K2 = the cell for which the result of the formula will be in

A2 = date on the sheet
Bills!B30 = amount due
Bills!B31 = amount paid
Bills!B32 = date due
Bills!B33 = date paid


if "Bills!B33" is Blank then
if A2 = Bills!B32
if Bills!B31 is blank then
let the amount displayed in K2 equal Bills!B30
else
let the amount displayed in K2 equal Bills!B31
end if
end if
else
if A2 = Bills!B33
if Bills!B31 is blank then
let the amount displayed in K2 equal Bills!B30
else
let the amount displayed in K2 equal Bills!B31
end if
end if
end if

To clarify my intent, the formula goes like this:

if the date paid is blank, then I'm paying on the date due
if the date on this column = the date due
if the amount paid is blank, then I intend to pay the exact amount due
otherwise I'll be paying more or less than the exact amount due
else, if the date paid is not blank
if the date on this column = the date i intend to pay
if the amount paid is blank, then I intend to pay the exact amount due
otherwise I'll be paying more or less than the exact amount due

Its just too complex for me. Any help is much appreciated.

Answer 1

i think this will give you what you ask for, but im not sure if its exactly what you want. give it a try.
from what i read:
b30 and b32 will always have numbers based on the bills
b31 and b33 will be manually filled in by you.

in a2 use
=IF(B33="",B32,B33)

in k2 use
=IF(B31="",B30,B31)

b31 and b33 have to really be blank...no zeros or placeholders

i had to throw this formula in b2 just to keep it straight for me:
=IF(A2=B32,"is the date the bill is due","is the date you paid the bill")

the k2 cell seems kind of odd. are you planning to partially pay some bills? but you only have 1 cell for date paid.
you are going to have to add an extra cell saying that you paid x amount, but you still owe the difference.

Happy Christmas

Answer 2

you do no longer say which version of Excel you utilize, yet in version 2003 and before, the cut back is 7 nested IF statements. so as meaning you are able to have 7 in the 1st =IF, for 8 finished.

Answer 3

Excel is a TERRIBLE tool to try and do such a thing; it's a database tool, not an accounting tool. Buya copy of QuickBooks, you won't have to work so hard and it might actually give you what you apparently want!

Answer 4

You can use a new product called Prism
it connects to your excel in 3 easy steps.

from there its just like office application that can solve your problem

Its in beta so its free now
you can find it in
http://www.sisense.com

hope it helps