+ Reply to Thread
Results 1 to 4 of 4

Problems adding extra conditions to an IF formula

  1. #1
    Registered User
    Join Date
    03-06-2013
    Location
    Prague
    MS-Off Ver
    M365, version 2304
    Posts
    40

    Problems adding extra conditions to an IF formula

    Hey all

    I'm having a problem adding extra conditions to a formula.

    I have two formulas in the table (attached) that work out the how long an unpaid invoice is overdue, and how much it is overdue by (in columns F and G respectively). Once an invoice has been paid in full, the data in these cells disappear.

    What I need to do is figure out a way to calculate these things where there may be incremental payments.

    The formula for the amount overdue is simple enough at the moment:

    =IF(E3<C3,C3-E3,"")

    But, I wanted to add something that says, if the Date of Invoice cell in the rows below is blank AND the sum of the rows in the below columns with blank date cells is less than the total invoice amount, then subtract the sum of the below cells from the invoice total. I was thinking of something like

    =IF(E3<C3,C3-E3,"",IF(AND(B4="",(E3+E4)<C3,C3-(E3+E4))))

    But that's just returning an error. Also, this only looks at the row below, whereas, I would need to be looking at maybe the ten rows below in case the invoice was paid in a number of increments.

    I also need to do something similar for the days overdue column - and the formula in that is a bit more complicated (although I'm sure I put it together in a fairly convoluted way!!!)

    I'm really just feeling my way along here - and advice would be really appreciated - I can't figure this out and it's sending me cross-eyed!!

    Thanks!

    Sample increments table.xls

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Problems adding extra conditions to an IF formula

    =IF(E3<C3,C3-E3,IF(AND(B4="",(E3+E4)<C3),C3-(E3+E4),"")))


    Syntax is if(Condition, True Result, if(2nd Condition,True Result,False Result))
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-06-2013
    Location
    Prague
    MS-Off Ver
    M365, version 2304
    Posts
    40

    Re: Problems adding extra conditions to an IF formula

    Hey - thanks. I completely forgot to include the False result at the end.

    While the second part of the formula works on its own now IF(AND(B4="",(E3+E4)<C3),C3-(E3+E4),""), it won't work with the first part of the formula included.

  4. #4
    Registered User
    Join Date
    03-06-2013
    Location
    Prague
    MS-Off Ver
    M365, version 2304
    Posts
    40

    Re: Problems adding extra conditions to an IF formula

    Ok - I seem to have fairly simplified this with:

    =IF(A4<A3,C3-(E3+E4),C3-E3)

    for column G. The problem with this is, it leaves a negative value for the likes of incremental cells (such as G4 in the attached spreadsheet). Also, I need this to look further than one cell down (maybe up to ten). I also have to figure out how to do all this for the Overdue column at F - which I think might be a bit more tricky. In particular, I would be grateful if anybody could suggest how I would increase the range of cells I'm looking at (in the above formula, I would like to know if any of the cells from A4 - A14 are smaller than A3, and, where they are, to add the corresponding value E4 - 14 to E3.

    Actually, just realised something else - would it be possible to put a break into the searching down function, so that, if, let's say, A5 is greater than A3, then it doesn't search any further down?

    Serious headache right now!!!

    Thanks!

    Sample increments table 2.xls

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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