Is there a function to perform this operation?
Let me see if I can explain this. I have these
range of cells: (D7,D37,D67,D97,D127,D157,D187,D217,D247,D277), and when I use the MIN() function =MIN(D7,D37,D67,D97,D127,D157,D187,D217,D247,D277) I get 0 because the values in the range are (1,5,0,5,0,0,1,3,1,2). But I want my result to be 1 because 1 is the next higher number to zero in the range. In the same instance, if the values in the range are (14,14,0,14,0,0,14,0,14,14) I want my result to be 14, because 14 is the next higher number to zero in the range. This is want I am trying to achieve. |
Is there a function to perform this operation?
See cpearson website
http://www.cpearson.com/Excel/lists.htm#Functions "Ayo" wrote: Let me see if I can explain this. I have these range of cells: (D7,D37,D67,D97,D127,D157,D187,D217,D247,D277), and when I use the MIN() function =MIN(D7,D37,D67,D97,D127,D157,D187,D217,D247,D277) I get 0 because the values in the range are (1,5,0,5,0,0,1,3,1,2). But I want my result to be 1 because 1 is the next higher number to zero in the range. In the same instance, if the values in the range are (14,14,0,14,0,0,14,0,14,14) I want my result to be 14, because 14 is the next higher number to zero in the range. This is want I am trying to achieve. |
Is there a function to perform this operation?
Assuming the numbers are always positive.
Try this array formula** : =MIN(IF((MOD(ROW(D7:D277)-ROW(D7),30)=0)*(D7:D2770),D7:D277)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Ayo" wrote in message ... Let me see if I can explain this. I have these range of cells: (D7,D37,D67,D97,D127,D157,D187,D217,D247,D277), and when I use the MIN() function =MIN(D7,D37,D67,D97,D127,D157,D187,D217,D247,D277) I get 0 because the values in the range are (1,5,0,5,0,0,1,3,1,2). But I want my result to be 1 because 1 is the next higher number to zero in the range. In the same instance, if the values in the range are (14,14,0,14,0,0,14,0,14,14) I want my result to be 14, because 14 is the next higher number to zero in the range. This is want I am trying to achieve. |
All times are GMT +1. The time now is 08:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com