I'm currently putting together a budgeting worksheet and I've hit a bit of a snag.

I believe the formula calls for "if" functions but maybe there's something better?

Should it look something like this?:


=if(C16<1.7=SUM(F16)),
if(C16<2.7=SUM(F16/2)),
if(C16<3.7=SUM(F16/3)),
if(C16<4.7=SUM(F16/4)),
if(C16<5.7=SUM(F16/5))

I also want to add another condition:

=if(f160=sum(d16/5))

I need the solution to appear in one cell, "E16"

key:

C16 = "weeks till due date"
F16 = "total left to pay"
D16 = "total cost"
E16 = "weekly payments"

Thank you very much. Any help is greatly appreciated.

Mark B

Hi Mark,

What happens if F16 is greater than or equal to 5.7?

Also, you've said you want that additional condition added. Do you mean added to the original formula or in another cell?

The reason I ask is, it doesn't fit in with the other conditions and could conflict with them giving spurious results.

S.

Hello Spencer,

Thanks for the speedy reply.

This portion of the budget is a continuing monthly event based on a (4) week to (5) week cycle. So I don't need it to divide the total due by any more than (5) weeks.

F16 represents the amount left to pay from the total.

Check out this example:

D16 = (-) $1,000 (total)
F16 = (-) $500 (left to pay)
C16 = (+) 5 (weeks till due)
E16 = (-) $100 (pay per week)

The extra condition "=if(f160=sum(d16/5))" comes in when F16 becomes a positive number. Once the total left to pay (F16) becomes a positive number, the number of weeks until the total is due (C16) becomes less important. The payments per week (E16) still needs to continue and be effective.

Like this example:

D16 = (-) $1,000 (total)
F16 = (+) $50 (left to pay)
C16 = (+) 2 (weeks till due)
E16 = (-) $200 (pay per week)

On a (-)$1,000 monthly total (D16) it's pointless for me to divide the total left to pay (F16) by the number of weeks until the total is due (C16) when (F16) is only (+)$50. The payments per week will be too small and will quickly end up back in deficit. So my solution is to divide the total due by the maximum number of weeks to pay (5) :

=if(f160=sum(d16/5))

This appears to me to be the best accounting solution for putting all the numbers in the plus. Unfortunately my knowledge of excel functions are limited. This is where I really need some help.

Thanks again.

Mark B

Hi Mark,

The below formula put into E16 covers all the conditions you've set out above, but I'm not 100% sure it does what you need anyway.

=IF(C16="","",IF(F160,D16/5,IF(AND(C160,C16<1.7),F16,IF(AND(C16=1.7,C16<2. 7),F16/2,IF(AND(C16=2.7,C16<3.7),F16/3,IF(AND(C16=3.7,C16<4.7),F16/4,IF(AND(C16=4.7,C16<5.7),F16/5)))))))

If this doesn't do what you want, then perhaps you would like to post an example workbook with dummy data and some notes explaining how it SHOULD work and what the results SHOULD be under certain circumstances, it might help us to give you a more helpful solution.

S.