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!

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!

Another option would be to have the SP formula (once) in it's once column, then just test that with your IF statement

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

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.