I need to add a statment to the formula below to say that if result is negative to display 0 rather than the negative number. Otherwise the normal result (positive) should display as normal. My question is, how should it be written and where do I embed it into the formula?
=SUMPRODUCT(--('Paste Jason''s Prospect Report'!$O:$O="MBAIE"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"CHB"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"GW"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"CH"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"CHC"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"GWB"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"GWC"),--('Paste Jason''s Prospect Report'!$B:$B='GRAD COMPARATIVE REPORT'!A5),--('Paste Jason''s Prospect Report'!E:E<>"NOT"),--('Paste Jason''s Prospect Report'!O:O<>"K12"),--ISNUMBER(MATCH('Paste Jason''s Prospect Report'!$E:$E,{"DENY","PEND","WDRAW"},0)))-SUM(J8:J15)
Thanks!
You would use a MAX
=MAX(0,your formula)
You can shorten the SUMPRODUCT by adopting as ISNA(MATCH test on Z tests in much the same manner as you have conducted an ISNUMBER(MATCH on Col E ... ie same construct but ISNUMBER becomes ISNA.
Try to avoid using entire column references with SUMPRODUCT/Arrays - even though you can do this XL2007+ you shouldn't - it's an "expensive" formula.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Ok, so does that mean it will look like this?
=MAX(0,SUMPRODUCT(--('Paste Jason''s Prospect Report'!$O:$O="MBAIE"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"CHB"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"GW"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"CH"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"CHC"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"GWB"),--('Paste Jason''s Prospect Report'!$Z:$Z<>"GWC"),--('Paste Jason''s Prospect Report'!$B:$B='GRAD COMPARATIVE REPORT'!A5),--('Paste Jason''s Prospect Report'!E:E<>"NOT"),--('Paste Jason''s Prospect Report'!O:O<>"K12"),--ISNUMBER(MATCH('Paste Jason''s Prospect Report'!$E:$E,{"DENY","PEND","WDRAW"},0)))-SUM(J8:J15)
That depends upon the significance of the subtraction of J8:J15 - ie whether or not that is exclusive of the SUMPRODUCT min of 0 requirement.
In terms of shortening per my post re: Z
=MAX(0,SUMPRODUCT(--('Paste Jason''s Prospect Report'!$O:$O="MBAIE"),--ISNA(MATCH('Paste Jason''s Prospect Report'!$Z:$Z,{"CHB",GW","CH","CHC","GWB","GWC"},0)),--('Paste Jason''s Prospect Report'!$B:$B='GRAD COMPARATIVE REPORT'!A5),--('Paste Jason''s Prospect Report'!E:E<>"NOT"),--('Paste Jason''s Prospect Report'!O:O<>"K12"),--ISNUMBER(MATCH('Paste Jason''s Prospect Report'!$E:$E,{"DENY","PEND","WDRAW"},0)))-SUM(J8:J15)
those in red are superfluous I think given the other conditions...
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Copied and pasted your formula. It said that there is an error with it. It would not specify where.
If we assume the MAX is meant to be inclusive of the subtraction of J8:J15 then based on my interpretation of your formula:
If the SUM is to sit outside of the MAX then alter the parentheses accordingly=MAX(0,SUMPRODUCT(--('Paste Jason''s Prospect Report'!$O:$O="MBAIE"),--ISNA(MATCH('Paste Jason''s Prospect Report'!$Z:$Z,{"CHB","GW","CH","CHC","GWB","GWC"},0)),--('Paste Jason''s Prospect Report'!$B:$B='GRAD COMPARATIVE REPORT'!A5),--ISNUMBER(MATCH('Paste Jason''s Prospect Report'!$E:$E,{"DENY","PEND","WDRAW"},0)))-SUM(J8:J15))
earlier error result of a) missing parenthesis in your first MAX formula b) missing " around GW in the ISNA(MATCH
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks