+ Reply to Thread
Results 1 to 11 of 11

Calculate based on age of data

  1. #1
    Registered User
    Join Date
    09-21-2011
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Calculate based on age of data

    Hi Everyone, I would like to enter a formula in column C that calculates a percentage of value based on the age of the entry.

    A B C
    1 Labour Date Labour Charge Revised Labour Charge
    2 09/12/2013 58.59
    3 20/05/2014 30.09
    4 11/06/2014 60.18

    Column C should contain the formula which calculates column B dependent on the age in column A, i.e. if Column A is >12 months, B2*0%, A is <=12 months and >9 months, B2*25%, A is <=9 months and >6 months, B2*50%, A is <=6 months and >3 months, B2*75%, A is <=3 months, B2*100%

    Any help with this would be greatly appreciated.

    Many thanks
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Calculate based on age of data

    Hi,

    Was 1,2,3,4 i are age ?

    Punnam

  3. #3
    Forum Contributor
    Join Date
    09-26-2014
    Location
    Moscow, Russia
    MS-Off Ver
    MSE 10, MSE 13
    Posts
    179

    Re: Calculate based on age of data

    Hello, Steve.

    Try this one. Is it fitting for your needs?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best wishes and have a nice day!

  4. #4
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Calculate based on age of data

    Hi ,
    Your requirement is not clear but if want to check on age this can be help full .
    =IF(I10>=12,B2*0%,IF(AND(I10>=9,I10<12),B2*25%,IF(AND(I10>=6,I10<9),B2*50%,IF(AND(I10>=3,I10<6),B2*75%,IF(I10<3,B2*100%)))))
    Punnam

  5. #5
    Registered User
    Join Date
    09-21-2011
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Calculate based on age of data

    Hi, Thanks for the reply, the 1, 2, 3, 4 are the spreadsheet rows (it has not formatted very well and looks better on the attached png file).

    Steve

  6. #6
    Registered User
    Join Date
    09-21-2011
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Calculate based on age of data

    Hi Rioran, Thank you for your reply, I have not managed to get this formula to work for me.

  7. #7
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Calculate based on age of data

    Hi Steve,

    Have you checked the nested if condition in post 4 ?

    Punnam

  8. #8
    Registered User
    Join Date
    09-21-2011
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Calculate based on age of data

    Hi Punnam, I have entered your formula as per the attached, however the it is not calculating correctly and returning zero values?
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Calculate based on age of data

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


    Punnam

  10. #10
    Registered User
    Join Date
    09-21-2011
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Calculate based on age of data

    Hi Punnam, Thanks for this this formula is still not calculating the correct percentages, I have now created a formula in a new column to calculate the age of the cost which I can then refer to in the formula you have given and this now works. Many thanks for your help on this.

    Kind Regards,
    Steve

  11. #11
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Calculate based on age of data

    HI SteveLaird,

    Thanks for the feedback , you can thank one who helped you by adding reputation Points to them

    Punnam
    Last edited by Punnam; 10-08-2014 at 08:37 AM.

+ 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. How to calculate the monthly sales based on the Data given ?
    By yjohari in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-09-2014, 07:10 PM
  2. automatically calculate results based off a raw data set
    By Panfergrrl18 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2013, 11:51 PM
  3. Calculate Sum of data based on date
    By sathyaanil37 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2012, 10:25 AM
  4. Calculate value using VBA, based on data in excel
    By NKOTB_VBA in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2011, 02:52 PM
  5. Macro that will calculate results based on data from other sheets
    By jack's in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2009, 12:30 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