Great tips and techniques! I tinkered myself and came up with these two which also accomplish the goal: =AGGREGATE(15,6,((A2:A7)^2)/(A2:A7),1) =AGGREGATE(15,4,A2:A7,COUNTIF(A2:A7,0)+1) Thanks for the insights and inspiration to experiment! Thumbs up!
The ARRAY approach is great. The only problem is when the data size is huge, it may hamper the working of the spread sheet. Thanks for this nicest trick :)
Thank you for the video. How can I add the If function to this relation? For example, I want to calculate only for a selected date written just next to the formula. So when I enter the date I want to bring the result for this range only.
Hi thank you for the video, can you please design the same formula with negative values as well. For eg. -1, 0, 1, 2,3,4,5.... now answers should be -1.... Pl explain how to find out minimum value ignoring zero but not negative values .....
Hi, great video. I am trying to create a formula for MAX that does not count blank cells but does count zeros. I am a novice at this and can't seem to figure it out. Currently, I am using this =IF(MAX(C4:E4)=0,"",MAX(C4:E4)), but if I have scores with zeros across the board, the total cell is blank...any help is much appreciated.
hi, i hve problems to apply the formula in pivot table, when im trying to summarize value by min, how can i exclude the zeros value...looking forward to your help..
Hi, I have a formula that calculates the number of win draw in a Lotto pool (=SUMPRODUCT(COUNTIF(C5:I5,Draw)) I would like to leave blank cells in place of zeros if there are no wins, can you tell me how to add another "countif" to solve this problem, your help would be much appreciated. regards, Don
Do you mean that you want those cells to show blanks instead of zeros? Because that requires a formula in the cell that displays the value. Ask in our forum and upload a sample file that shows what you're trying to do and it will be much easier to help. www.teachexcel.com/talk/microsoft-office?src=yt
Hi I like to nest the above fromula with this formula MIN(IF($A$3:$N$235=B253,$N$3:$N$235)) can u help ? However the following formauls is not wkg MIN(IF($A$3:$N$235=B245,$N$3:$N$235),$N$3:$N$2351)
In Excel 2013, this MIN function is annoying. I have column D with numbers. There will be some numbers less than 0, which I do not want MIN to use. I want MIN to ONLY display the MIN above 0 and ignore any minus number. Logically, I thought that this should work: =MIN(IF(D:D>0,D:D)) In any program coding, that would just work. IF criterial is > 0, the do stuff. But here, it is totally ignore by Excel. If I enter, as a test, -10, then -10 is displayed, even though, it's not above 0! I also tested with a specific range, like D3:D50, but no difference. Does anyone have an idea why this doesn't work and/or a way to get this to work? In this example of actual numbers from my sheet: 09.50 30.00 *01.50* 12.00 -10.50 I want *01.50* to show as the MIN. But -10.50 is displayed. ???? Ideas?