LinkBack URL
About LinkBacks
If the result of my sumifs formula is <0 or >40,000 is there a way to make it show up as 0 instead?
If the result of my sumifs formula is <0 or >40,000 is there a way to make it show up as 0 instead?
Hi, welcome to the forum
Maybe this...
=IF(or(a1<0,A1>40000),0,A1)
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
Is it possible to actual have it included in the original formula or do I have to do this as a reference to the cell with the formula in it? Below is the original formula that I am using.....
=IFERROR(SUMIFS('Average Timeframe to Settlement'!$G:$G,'Average Timeframe to Settlement'!$C:$C,Sheet1!$B3,'Average Timeframe to Settlement'!$A:$A,Sheet1!E$1)-SUMIFS('Average Timeframe to Settlement'!$F:$F,'Average Timeframe to Settlement'!$C:$C,Sheet1!$A3,'Average Timeframe to Settlement'!$A:$A,Sheet1!E$1),0)
Just replace my A1 reference with your formula
Never mind, he got it!
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Sambo, have you seem that 1 insurance commercial with the girl trying to get the dollar from the guy with the fishing rod?.....ya gotta be quicker than that LOL
Insert your original formula (without the beginning =) in this formula where I have A8
=MIN(40000,MAX(0,A8))
Not quite right, never mind.
Last edited by Jonmo1; 01-17-2016 at 11:17 PM.
This was almost right, but not quite
Here's an adjustment to this that would reduce the number of times you have to do the sumifs.Originally Posted by Jonmo1
It's done 3 times in the nested IF.
This would only need to do it twice
=IF(A8>40000,0,MAX(0,A8))
Yeah, iPads, hunting and pecking can take a while.. But the portability is nice.
Wonderful, thanks for you help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks