+ Reply to Thread
Results 1 to 9 of 9

Help with Executing Formula based on IFS conditions

  1. 05-19-2022, 08:59 PM #1
    kvallecillo
    kvallecillo is offline
    Registered User
    Join Date
    09-28-2018
    Location
    El Salvador
    MS-Off Ver
    365
    Posts
    21

    Unhappy Help with Executing Formula based on IFS conditions

    Hello Everyone,

    I am trying to create a straight line depreciation template and I would like to automate as much as I can. I have two formulas that I would like to condition each, but I am having trouble doing so. I would like for one of them to execute if the year I purchase the asset is equal to the year in the column so I can get the depreciation amount equivalent to the days left in the accounting period. Both formulas work separately, but I can't make them work together. Any help will be appreciated. Thanks in advance.

    Formula 1:

    IF(YEAR(date of purchase)<=Year column,(IF(Remaining Depreciation >Salvage, Annual Depreciation," "))," ")

    Formula 2:

    ROUND(((Annual Depreciation/365)*(Date(year(date of purchase),12,31)-date of purchase))+1,2)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-20-2022, 02:31 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,247

    Re: Help with Executing Formula based on IFS conditions

    The sample workbook contains no columns of data with which to test your formulae.

    Please provide a proper sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 05-20-2022, 05:49 AM #3
    kvallecillo
    kvallecillo is offline
    Registered User
    Join Date
    09-28-2018
    Location
    El Salvador
    MS-Off Ver
    365
    Posts
    21

    Re: Help with Executing Formula based on IFS conditions

    Hi, thanks for responding. I have added an example as an extra sheet in the workbook.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 05-20-2022, 06:00 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,247

    Re: Help with Executing Formula based on IFS conditions

    Thank you. Where will I find the two formulae you wish to combine?

    One row of data is not enough: you need two at an absolute minumum so that you can mock up two outcomes - one that will return the result of one formula and the other for the other. Sorry, but we are not there yet.
    Last edited by AliGW; 05-20-2022 at 06:03 AM.
    Reply With QuoteRegister To Reply
  5. 05-20-2022, 06:23 AM #5
    kvallecillo
    kvallecillo is offline
    Registered User
    Join Date
    09-28-2018
    Location
    El Salvador
    MS-Off Ver
    365
    Posts
    21

    Re: Help with Executing Formula based on IFS conditions

    Hi,

    Thanks again for the reply. The formulas begin with the year 2007 on cell Q4
    Reply With QuoteRegister To Reply
  6. 05-20-2022, 06:37 AM #6
    kvallecillo
    kvallecillo is offline
    Registered User
    Join Date
    09-28-2018
    Location
    El Salvador
    MS-Off Ver
    365
    Posts
    21

    Re: Help with Executing Formula based on IFS conditions

    Hi,

    I just added another row with another example. Thanks again
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 05-20-2022, 06:44 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,247

    Re: Help with Executing Formula based on IFS conditions

    Thanks - the workbook is much more helpful
    Reply With QuoteRegister To Reply
  8. 05-20-2022, 06:54 AM #8
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Help with Executing Formula based on IFS conditions

    try below formula in Q4, copy and paste it across

    =IF(YEAR($F4)>Q$3,"",IF(YEAR($F4)=Q$3,ROUND((($N4/365)*($G4-$F4))+1,2),MIN($M4-SUM(P4:$Q4),$N4)))
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  9. 05-21-2022, 09:09 PM #9
    kvallecillo
    kvallecillo is offline
    Registered User
    Join Date
    09-28-2018
    Location
    El Salvador
    MS-Off Ver
    365
    Posts
    21

    Re: Help with Executing Formula based on IFS conditions

    Hi,

    Thanks a lot for the formula. It works wonders!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] If formula based on multiple conditions
    By T.Turner in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-04-2020, 11:43 AM
  2. [SOLVED] Formula based on two conditions?
    By LMills77 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-21-2019, 03:43 PM
  3. [SOLVED] if formula based on several criteria to extract data based on conditions
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-17-2018, 12:46 PM
  4. [SOLVED] Executing Macro based on cell contents
    By m_roussakis in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-28-2018, 09:33 AM
  5. Finding next value based on conditions in a formula
    By Lithium in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2014, 10:02 AM
  6. Executing a macro based on worksheet change
    By Rynofasho in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-07-2010, 08:44 PM
  7. [SOLVED] Executing a formula based on criteria being met
    By confused teacher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2006, 02:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1