How to shorten if statements that refer to full formula

1. 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!

2. 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!

3. 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. 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. Re: How to shorten if statements that refer to full formula

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

6. 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. 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

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

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

9. 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.

10. 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.

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