I am using the following code:
=AA7-MIN(AA7:AA1)
Occasionally, there are "0" numbers in the range. I don't want to include the "0" when finding the low number. How do I add this to the code?
I am using the following code:
=AA7-MIN(AA7:AA1)
Occasionally, there are "0" numbers in the range. I don't want to include the "0" when finding the low number. How do I add this to the code?
Try utilizing SMALL() with COUNTIF()
=AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)
One way:
=AA7-SMALL(AA7:AA1, COUNTIF(AA7:AA1, 0)+1)
I'm not sure I'm 100% sure of what you're trying to do but heres a shot in the dark
are you using the formula "AA7-MIN(AA7:AA1)" to create a new series and you want to exclude all entries where "AA7-MIN(AA7:AA1)=0" then you could nest it in an if command like the following:
=if(AA7-MIN(AA7:AA1)=0,"",AA7-MIN(AA7:AA1))
now when AA7-MIN(AA7:AA1) = 0 the calculation will dislay a blank and will not be used in calculating the min.
in general if you've your series on column A you could create a new cleaned series in an adjacent column by using the formula "if(a1=0,"",a1)"
It depends on what you mean by eliminate the minus number.
You can use MAX() to limit the result to zero or whatever other value you want.
You can use IF() to test for neg result and provide an alternative.
You'll have to be more specific.
I tried this. It presents another problem. The result, when left blank, does not work with another formula that it is included in.
The other code is:
=IF($C7<D$6," ",+IF($C7<(D$6+18),"*",+IF($C7<(D$6+36),"* *","* * *")))
What happens in the 18 cells using the above code, is each cell get *** included instead of a blank.
Another brain stormer.
Try this:
=IF(SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)>=AA7,"",AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1))
assuming you mean this "AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)" is the handicap.
Thanks, but this doesn't work with the other code I have for the cells using the *** data.
I think I am going to just use this code: =AA7-SMALL(AA7:AA1, COUNTIF(AA7:AA1, 0)+1) and deal with the minus number. I can white it out on the few occasions a minus number appears.
Thank you all for your help...again.
I have another question using this similar problem.
I also have a code that provides 80% of the difference from the lowest number in the range. The code is: =ROUND((AA24-MIN(AA24:AA29) )*0.8,0)
How can I get the same results when there is a "0" in AA24:AA29, and I don't want the "0" included in the MIN range.
What the range would be:
AA24 = 29
AA25 = 0
AA28 = 11
AA29 = 0
The smallest number would be 11 (if I don't count the 0s). The difference, between the 29 and 11 is 18. At 80% it is rounded to 14.
The code above works fine as long as there are no zeros. With zeros, it is incorrect.
Anybody want to take a crack at this one?
Your new question is the same as your original question. You've just changed the range.
@Roger
Your formula will result in AA7-1 when there is a 0 in the range because you are saying use the MAX of 1 and the MIN of the range so 1 will be the MAX of 1 and 0. I don't think OP wants that.
Last edited by Cutter; 03-26-2011 at 08:04 PM.
You're quite right.Originally Posted by Cutter
I had totally misread the Op's requirement.
Last edited by DonkeyOte; 03-27-2011 at 05:51 AM. Reason: fixed tag
Hi
I think the array entered formula as follows, will do what you want.
To array enter, use Control+Shift+Enter (CSE) when you enter or edit the formula.
When you use CSE, Excel will insert curly braces around the formula - do not type them yourself.
Please Login or Register to view this content.
just a question
AA24 = 29
AA25 = 0
AA28 = 11
AA29 = 0
will a29 always be the largest? when used =ROUND((AA24-MIN(AA24:AA29) )*0.8,0)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks