Excell formula help, please?

[KatyMac]

I have a total, over the year the total will reduce

So in April it's say £3600/12
& in May it's £3250/11
& in June it's £3000/10

So if I use "=" and the cell number for all 12 months, the months that have happened will change as each new month appears

But if I use "=" cell then "-" Aprils cell it becomes a circular reference

So how can I do it?

Not sire exactly what you're skint but sounds like you need more columns! If you emaill it to me ill be able to help tomorrow, will that be any good?

Sire should be sire
Skint should be asking

I am not sure what you are describing - are you taking a figure off each month that you are inputting somewhere else?

Anyway. In excel = means that you are going to use a formula. If you want to do a simple calculation you put + at the front.

Try doing + cell - april's cell

Thanks Stealth, but if I am capable I'd like to work it out (but let's be honest I'll probably end up sending it to you )

I'll try again not very good at this

How much do I have left
April 3600/12
May 3250/12
Jun 3000/12

So in April I used 300, May 295.45, Jun 300 & so on

But the amount don't change until the month happens; so right now the total is 3600 & April is 300. In May when the total drops to 3250, Aprils figure will also drop to 3250/12

I think?

I can't quite get what you want to do either, is it calculate that if this month is April, the total left to pay off is TOTAL - April's cumulative balance?

What is it you're trying to show? Why would aprils figure drop retrospectively?
Another column seems the obvious solutioon: original April and adjusted April?

And do all previos months drop when you add a new month, so in march will April have 11 values taken off?

Let me try to understand what you are trying to do.

You have an amount of money, £3600, to last 12 months, and so the budget at the start of the year is allocated as £300 per month.

However, as the month passes, you want to reallocate the remaining funds equally over the remaining months?

I have 8 people annual leave records

I have a figure which is how much AL they have left cumulatively I've done this by adding up the amount of AL they each have left and then dividing by 12 so I have a monthly figure

Then that figure is put in another spreadsheet called AL left

Would it work if I divided the total figure by 12 for April & by 11 for May & by 10 for June?

I think ClaireAll explained it better

If I am right as to what you want:

column A is all your headings.

cell b1 is 3600
Cell b2 is b1/12
Cell b3 is (b$1-b2)/11
Cell b4 is (b$1-b2-b3)/10 etc

Right so there's a forecast amount for the month and then at the end of the month an actual amount for that month, which may affect the forecast amount for all the months that follow? (If it actually is leave I'm not sure I'd track it this way since logically people don't use an average amount of leave days per month, it will have natural peaks and troughs).

I would track actual and forecast in two different columns, so that forecast is recalculated from the actual when you enter it at the end of the month. If you want it to calculate the remaining months of this year you can use MONTH()-12.

No it's not their leave I am tracking is the allowance I put in to pay for cover

I think that makes sense; I'll see if it works

That was confusing, for some reason I didn't see it was your thread, then you seemed to pop up and start talking as of it was your thread. I've only just realised you are the op. That was really odd!

Now I'm so confused, it is my thread....honest

It works - thanks

:o must have had brain freeze! Glad you for it sorted