+ Reply to Thread
Results 1 to 10 of 10

How to shorten if statements that refer to full formula

  1. #1
    Registered User
    Join Date
    07-28-2019
    Location
    Atlanta, United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    How to shorten if statements that refer to full formula

    I'm creating a template that organizes some data from an external, hard-coded workbook. Some calculations from the external workbook were coming in as incredibly small numbers (i.e. 0.0000872...), which for my purposes is equivalent to zero. But using the accounting format, those numbers show up as 0 or (0), instead of "-". I adjusted my formula to the following, which basically says if the answer is greater than -1 or less than 1, than it's zero. But it makes the formula incredibly long becuase using the IF statement, and embedding the AND statement, require the formula to be repeated 3 times, which will be confusing for anyone trying to jump in. Any suggestions for a simpler formula? Also note, sometimes the answer would show up as greater than .5, which is also zero for my purposes. So using ROUND also isn't necessarily ideal.

    =IF(AND(SUMPRODUCT(('CF Drop'!$A$78:$A$240=TemplateCF!$B133)*('CF Drop'!$E$74:$IV$74=TemplateCF!J$6)*('CF Drop'!$E$78:$IV$240))<1,SUMPRODUCT(('CF Drop'!$A$78:$A$240=TemplateCF!$B133)*('CF Drop'!$E$74:$IV$74=TemplateCF!J$6)*('CF Drop'!$E$78:$IV$240))>-1),0,SUMPRODUCT(('CF Drop'!$A$78:$A$240=TemplateCF!$B133)*('CF Drop'!$E$74:$IV$74=TemplateCF!J$6)*('CF Drop'!$E$78:$IV$240)))

    Thanks!
    Last edited by Barren_Wuffett; 07-28-2019 at 03:04 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,090

    Re: How to shorten if statements that refer to full formula

    Welcome to the forum.

    I am NOT going to try to back-engineer your IF statements because there MUST be an easier way! In order to determine what that might be, we'll need to see a workbook.

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    PS Like the user name - LOL!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to shorten if statements that refer to full formula

    You don't need to test positive and negative separately, just use ABS to flip any negatives in the logical test.

    =IF(ABS(SUMPRODUCT(....))<1,0,SUMPRODUCT(....))

  4. #4
    Registered User
    Join Date
    07-28-2019
    Location
    Atlanta, United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: How to shorten if statements that refer to full formula

    AliGW, thanks great to be here!

    Everything within the IF statement is exactly what I want it to be. The issue is just not having to repeat the sumproduct formula multiple times within the IF statement due to getting tiny numbers from the original data. So I'll give a simplified example of what it says.

    =IF(AND(Formula<1,Formula>-1), 0, Formula)

    I don't want to list the formula more than once, let alone three times.

  5. #5
    Registered User
    Join Date
    07-28-2019
    Location
    Atlanta, United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: How to shorten if statements that refer to full formula

    This definitely helps cut it down a bit, thanks jason.b75

  6. #6
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to shorten if statements that refer to full formula

    Thinking of a different approach

    =--TEXT(SUMPRODUCT(….),"[>1]#0.0#######;[<-1]-#0.0#######;\0;")

    This will round to 8 decimal places, if you need greater (or lower) accuracy, simply adjust the number of # symbols in each group, first set is for positive numbers, second set for negative.

    Apply your accounting format to the cells as normal.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,996

    Re: How to shorten if statements that refer to full formula

    Another option would be to have the SP formula (once) in it's once column, then just test that with your IF statement
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,090

    Re: How to shorten if statements that refer to full formula

    SUMPRODUCT in a LOOKUP statement is what I was toying with.

  9. #9
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to shorten if statements that refer to full formula

    I thought about that, Ali, then decided text formatting the results was an easier option.

    I tried custom formatting to eliminate the extra functions entirely, but that didn't work

    I recall a simple-ish UDF from a few years ago for this purpose, The function was simply called V(), courtesy of Harlan Grove if I remember correctly. I'll have a look, see if I can find it.

    Formula would be something like =IF(AND(V(SUMPRODUCT(...))<1,V()>1),V(),0) where the sumproduct calculation is nested in the first instance of V() and the subsequent instances are left empty.

    The function was defined something like
    Please Login or Register  to view this content.
    Whatever is passed to the optional arguement in the first instance would be set as the value of V() and retained to be reused for any further instance of V() with the optional argument missing.
    Last edited by jason.b75; 07-28-2019 at 05:13 PM.

  10. #10
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to shorten if statements that refer to full formula

    Found the code eventually,
    Please Login or Register  to view this content.
    =IF(ABS(V(SUMPRODUCT(....)))<1,0,V())

    Probably the easiest way to shorten repeated chunks of formula without extra cells or defined names.

+ 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. [SOLVED] Shorten IF OR formula
    By amros in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2017, 01:05 AM
  2. [SOLVED] How to shorten this sum formula?
    By mso3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-11-2016, 11:28 PM
  3. [SOLVED] How to shorten If formula?
    By mso3 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-03-2014, 01:01 AM
  4. Trying to shorten if then statements
    By pleasesmile in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 10:17 AM
  5. [SOLVED] Shorten formula
    By troysie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-03-2012, 10:24 AM
  6. To shorten the formula
    By mingali in forum Excel General
    Replies: 1
    Last Post: 06-15-2010, 12:47 PM
  7. How can i shorten my formula
    By Naiomi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-12-2008, 05:42 PM

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