Need a fix for an excel formula that's confusing the hell out of me...

Soldato
Joined
2 May 2011
Posts
12,272
Location
Woking
Morning,

As per the title, need some help with a formula that I just can't get my head around. I have a spreadsheet for the household bills. Mostly it works by putting x amount each month for each item into a certain account and having a total.

For bills that aren't monthly, so are annually, I want it to accumulate money each money. For example, car insurance is say £400 so I need to put in 400/12 each month.

I have a column that shows the total amount required, one that shows the monthly amount to put in, and one that shows when it needs to be paid. So car insurance is due in October (10), so it should now have 400/12 * 11 in another column.

I keep getting very close to the right formula but I can't quite get it...for example I have road tax due in June (5) for £150. It's currently showing £100 for that, so it's doing 8 months total not 8 months until it's due....

Can we say for simplicity that monthly amount is column A, annual amount is B, month is C.

Thanks geniuses.

EDIT: fixed it. turns out I'm the genius actually, not you guys

I adjusted the formula in the end to remove the 12-xxxxx-12 in the middle so it ended up as:

=IF((12-$C$2+$B4)>=12,-$C$2+$B4,12-$C$4+$B4)

eQupHGG.png
 
Last edited:
so you want a forumla that takes the remaining total for the bill (ie how much you have to save), and divide it by the number of months until said bill?

That wouldn't necessarily work because you're not saying you need to accumulate all the money in the next 7 months or whatever. You're always getting it over 12 months.

I think the key is just working out nicely how many months until the one you're interested in.
 
That wouldn't necessarily work because you're not saying you need to accumulate all the money in the next 7 months or whatever. You're always getting it over 12 months.

I think the key is just working out nicely how many months until the one you're interested in.

i was thinking more that "road tax is X, i've saved Y for road tax leaving X-Y=Z to go, it's 8 months till it's due so i need to save Z/8 per month to have enough"

i'm wondering if it's a case of reducing the months down to numbers (you can use "=month(date you want)" to return as a formula)
 
i was thinking more that "road tax is X, i've saved Y for road tax leaving X-Y=Z to go, it's 8 months till it's due so i need to save Z/8 per month to have enough"

i'm wondering if it's a case of reducing the months down to numbers (you can use "=month(date you want)" to return as a formula)

That makes sense, but then I need to automate the 8 months to go bit which is what I can't work out! Probably something really simple but also it's quite infuriating. Definitely reducing it to numbers as you say.
 
Total for year, divided by 12 (assuming you get paid monthly) + a bit extra. Put that monthly amount into your bills pot each month so you're not having to account for large bills.

I don't see why you need to know specific month to month bills.
 
Total for year, divided by 12 (assuming you get paid monthly) + a bit extra. Put that monthly amount into your bills pot each month so you're not having to account for large bills.

I don't see why you need to know specific month to month bills.

Because I want it to be super accurate. I think I've worked out what to do anyway.

eQupHGG.png
 
If I read your op correctly then you just need to calculate months since last payment and take that as a proportion of the total.
Various ways depending on how you've got it set up. Counting rows/columns. Calculation on months since payment date etc
 
Look after the pennies and the pounds look after themselves i guess.

But life it too short, just divide it by 12 then add 5% like normal people :)
 
Look after the pennies and the pounds look after themselves i guess.

But life it too short, just divide it by 12 then add 5% like normal people :)

Hah no thanks. The reason for being so pedantic is that I need to know what we pay out monthly and what we pay out annually and have no extra just sitting around. So I work out each of them and therefore have no surprises if I forgot xx bill. Works for me :) Shame my formula was wrong though, but I'd put away maybe £200 more than I'd need to so that's gone into savings.

I suppose with interest rates rock bottom these days, it probably doesn't matter if it's in a savings account or not, but I'm happy with it there.
 
True enough, I just use a cash ISA for the pittance it gives me and the excess from each year just gets bunged in premium bonds.

Unless you have a fair amount stashed away a small win dwarfs any interest rate.
 
True enough, I just use a cash ISA for the pittance it gives me and the excess from each year just gets bunged in premium bonds.

Unless you have a fair amount stashed away a small win dwarfs any interest rate.

Crazy isn't it. You can do all the cashback schemes, ISAs, points schemes whatever and none of it will make the slightest bit of difference when you can save £100 somewhere.
 
Back
Top Bottom