Continuous IF (AND?) Formula..

Displayed below is a set of general calculations showing a weekly budget across 5 weeks.

Weekly Budget = £124.84

Total Spent

Week 1 Week 2 Week 3 Week 4 Week 5
£124.84 £124.84 £54.84 £94.84 £94.84

Remaining

Week 1 Week 2 Week 3 Week 4 Week 5
£0.00 £0.00 £70.00 £30.00 £30.00

Current Saving = £130.00

Divided across each week (with £ remaining) = £43.33

As shown, the weekly budget is £124. 84; the top half of the data shows how much has been spent each week, and the bottom half shows the corresponding amount left for each given week. I have then got a formula showing the 'current saving' beneath the data set which adds together the remaining money.

The problem I'm having is with the following formula shown beneath the 'current saving' formula, known as 'divided across each week (with £ remaining)'.

What I'm trying to do is divide the 'current saving' amount across each week which still has money remaining. So far, I've got the following formula in place: =IF(AND(B63<=0,G63/4),IF(C63<=0,G63/3,AND(D63<=0,G63/2,IF(E63<=0,G63/1)))) - This has worked up until there is no more money left for week 3 as the formula still divides the 'current saving' by 3, even though there is only 2 cells with money remaining.

If anyone could help me out on this I would really appreciate it!

Have you though about using AVERAGEIF ? The condition being greater than zero.

Hope this helps.

Pete

Thanks for the quick response Pete. I was using the AVERAGEIF prior to the formula I'm currently trying to implement; I may end up using that as it is effective, just I was hoping for something more automatic as the AVERAGEIF relies on me changing the range in line with how many weeks with money left in them are remaining. But unless there is another formula that fits my request I'll settle with that. Thanks again.

Try this:

=AVERAGEIFS(B63:F63,B63:F63,">0")

I'm not sure why you need to adjust the ranges.

Hope this helps.

Pete