+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    03-14-2016
    Location
    Australia
    MS-Off Ver
    Not sure
    Posts
    6

    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
    Attached Images Attached Images

  2. #2
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If your questions has been answered to your satisfaction please don't forget to do the following:

    Add Reputation ... and ... Mark Thread as Solved

    Thanks,

    Ma 10:8b Freely you have received; freely give.

  3. #3
    Registered User
    Join Date
    03-14-2016
    Location
    Australia
    MS-Off Ver
    Not sure
    Posts
    6

    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. #4
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    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: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-14-2016
    Location
    Australia
    MS-Off Ver
    Not sure
    Posts
    6

    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
    Attached Files Attached Files

  6. #6
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    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: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,646

    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. #8
    Registered User
    Join Date
    03-14-2016
    Location
    Australia
    MS-Off Ver
    Not sure
    Posts
    6

    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. #9
    Registered User
    Join Date
    03-14-2016
    Location
    Australia
    MS-Off Ver
    Not sure
    Posts
    6

    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. #10
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

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

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-14-2016
    Location
    Australia
    MS-Off Ver
    Not sure
    Posts
    6

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to tell when its week 1 or Week 2 of a any given month
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-16-2014, 10:23 AM
  2. How do I create a rotating roster for a 10 week on 2 week off schedule
    By MacknMeggs78 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-02-2014, 09:18 PM
  3. [SOLVED] Showing Fridays of each month in a 4 week month and a five week month
    By david_j_p in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-09-2013, 06:27 AM
  4. How to: identify repeat offenders within a 3 week period (week per week basis)
    By Heloc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2012, 12:43 AM
  5. Replies: 10
    Last Post: 01-22-2012, 10:05 AM
  6. Create a week begining calender with custom week number
    By rds2472 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2009, 05:02 AM
  7. Averages - Day of the Week, Week, Month
    By njexpress9 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2009, 06:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1