# Create a formula that recognises a 4 week vs 5 week month for budget savings

1. ## Create a formula that recognises a 4 week vs 5 week month for budget savings

Hi

I want to calculate how much i have saved from my bill budget for the month (\$2250 for a 5 week month, and \$1800 for a 4 week month).

I have 5 columns for each week of the month with a formula at the bottom that tallys how much i have spent of my \$450 weekly budget (A12-Q12).

In the purple cell (on the image attached) I want a formula that can factor in whether it is a 5 week or a 4 week month. So if week 5 column has a \$0 figure in the tally (Q12), then it must be a 4 week month so minus what I have saved from \$1800, not \$2250. Alternatively, if the week 5 column has a figure of >\$0, then it is a 5 week month so minus what i have saved from \$2250.

Put simply,

IF Q12 = >\$0
U12 = (A12 + E12 + I12 + M12 + Q12) - 2250

BUT IF Q12 = \$0

U12 = (A12 + E12 + I12 + M12) - 1800

How do i get this into one formula?

Thanks

Anita

2. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

Thank you so much for your response.

I have inputted this formula into the spreadsheet and the first part of the statement works but the \$1800 part of the statement doesn't. Also, we got it backwards the sum of the cells is to be subtracted from the solid figure (e.g. 2250-(a12+.......).

What does this mean "\$q\$12"?

Anita

4. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

@AnitaNed,
The \$Q\$12 is cell Q12, but it is telling excel that you want the formula to always point to that cell even if you copy or fill the formula to other cells.
If you could upload your worksheet it would be easier to tell exactly what is going wrong with the formula, but in the interim try this...
Formula:
`Please Login or Register  to view this content.`

5. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

Thanks again. If you have a look at the attached spreadsheet the formula provided is still coming out inaccurately for \$1800(4 week values only).

NB: The \$ values for "Food, Personal, Petrol" are sum figures that correspond to the table at the bottom of the spreadsheet.
Each of these tally figures for food, petrol and personal are subtracted from \$450 (in CELL A12 to Q12) which is our total weekly budget for those three things.

You are awesome for helping. This has been stumping me for ages.

A

6. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

Ok got it...it was because in a 4 week month Q12 is blank not zero...Amended formula
Formula:
`Please Login or Register  to view this content.`

7. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

If you want to sum all exp of month:
=SUM(B9:T11)

Or sum the savings of the month:
=SUM(A12:T12)

8. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

LEGEND cplettner!! that is really great. thank you. seeing as you have met this challenge so well, i was wondering if there is now a way to return a blank cell if A12 is blank? I know how to do this with the standard IF formula, however this is now getting a bit out of my depth!

If not, all good. i can live with the fact that the maths works properly now.
thanks again for all your help

9. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

thanks for response bebo021999. we were needing to subtract from another figure as well, namely the amount we allocate for our bills.

10. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

Originally Posted by AnitaNed
i was wondering if there is now a way to return a blank cell if A12 is blank?
Do you mean in U12??

Formula:
`Please Login or Register  to view this content.`

11. ## Re: Create a formula that recognises a 4 week vs 5 week month for budget savings

Hey cplettner, i did mean A12. that way if the sheet is blank there is no figures hanging round. that formula works perfectly. Really appreciate your expertise with this. No way i would have managed it. Thanks a mill

There are currently 1 users browsing this thread. (0 members and 1 guests)