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

dirtychinchilla · 28 Sep 2020 at 08:36

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)

adolf hamster · 28 Sep 2020 at 09:20

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?

dirtychinchilla · 28 Sep 2020 at 09:25

adolf hamster said:
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.

adolf hamster · 28 Sep 2020 at 09:50

dirtychinchilla said:
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)

dirtychinchilla · 28 Sep 2020 at 09:55

adolf hamster said:
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.

Nutty_Death · 28 Sep 2020 at 10:03

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.

dirtychinchilla · 28 Sep 2020 at 10:13

Nutty_Death said:
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.

Nutty_Death · 28 Sep 2020 at 10:17

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

adolf hamster · 28 Sep 2020 at 10:26

dirtychinchilla said:
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.

=Month(date of bill)-Month(today())

For dates in the same year at least

alec · 28 Sep 2020 at 10:37

this belongs in https://www.overclockers.co.uk/forums/community/html-graphics-programming.41/

dirtychinchilla · 28 Sep 2020 at 10:57

alec said:
this belongs in https://www.overclockers.co.uk/forums/community/html-graphics-programming.41/

Probably, but you don't get much traffic there

Anyway, fixed it. Thanks everyone.

SPG · 28 Sep 2020 at 11:45

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

dirtychinchilla · 28 Sep 2020 at 11:47

SPG said:
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.

SPG · 28 Sep 2020 at 11:55

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.

dirtychinchilla · 28 Sep 2020 at 13:18

SPG said:
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.