Hello Friends
Please find the attached file (Max and Min value between 2 dates omitting zeros and blank.xlsx)
I need to find the max and min values by formula in light yellow cells.
Please omit / ignore the blank cells and zero cells for minimum and maximum calculation.
thanks in advance
Sekar
Put in G2:
=MAX(($A$1:$A$30>=$E2)*($A$1:$A$30<=$F2)*($B$1:$B$30<>"")*($B$1:$B$30>0)*($C$1:$C$30))
Put in H2:
=MIN(IF($A$1:$A$30<=$E2,IF($A$1:$A$30<=F2,IF(C1:C30>0,IF(C1:C30<>"",C1:C30)))))
and make sure press all together CTRL+SHIFT+ENTER button (array formula style)
and then copied down as necessary
cheers
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
in G2
Formula:
Please Login or Register to view this content.
dont need zero check for max unless you have all negative numbers
in H2
Formula:Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hello Azumi and humdingaling
Thanks for both of your formulas.
thanks again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks