non-zero

  1. A

    Which formula to use

    Hi all I have 3 columns of data. In the first and second columns I have a series of values and in the third column I need to use a formula which looks at the second column to see if there's any non-zero positive number there. If there is then it must use that number and it must thereafter look...
  2. A

    Returning average of the first N NON-zero values (incl neg values) of a column

    Hello New to the forum. I was wondering if somebody could be so kind as to help me with the following, which I have struggled with for hours now wihtout finding a solution to. I am trying to derive a function which generates the average of the first N non-negative values of a columns...
  3. B

    AVERAGE previous and next non-zero values over variable number of rows

    Trying to figure out a way that I can populate a column with the average of the next and previous non-zero values and divide the difference between the rows that separate them. Consider the following. I want to take the first column, and have a function that returns the values in the second...
  4. M

    Return non-zero data and associated row header from an array

    I will be grateful for help with the following I have an array in the following manner: 31/01/2018 28/02/2018 31/03/2018 30/04/2018 31/05/2018 30/06/2018...... First 120 0...
  5. D

    VBA Solver - Multiple ranges of variables and constraints

    Hello All, I am using VBA in order to automatize my Solver on multiple problems. I want to run the solver taking only the range of parameters, constraints and decision variables for which the value is non-zero. And I want to related to theses ranges created in the solver. Here is part of my...
  6. M

    Find Second Non-Zero Value In A Column

    Hi there, I'd like to find the second non-zero value in a range (A13:A200) and have the output be in A9. So, in the example below, "Wilson, John" would be the output in A9. Hope this is easy. Thanks in advance! 0 0 0 Smith, Joe 0 0 Wilson, John Jones, Sally 0 0 Johnson, Eric 0 Clark, Bill 0
  7. G

    I need the cursor to go to the last non-zero in column C

    Hello, I've got a macro for printing labels. I need the cursor to go to the last non-zero in column C after capturing the information and printing the label. I wrote this macro with the macro recorder. So, I know that I'll have to hard-code this last function. I don't know how to do this in VBA...
  8. F

    Need to get average of last 10 non-zero values in a row (grows daily)

    I have a spreadsheet with hundreds of rows that grows daily. I'd like to set up a cell at the top that'll display the average of the last 10 non-zero, non-blank rows and adjust each time a new value is added. I've tried many things, all to no avail. Thanks in advance.
  9. A

    Looking up NON - ZERO numbers, using Formula

    Thank you for helping me! :) I am trying to do a lookup that will pull the first Non-Zero # in a column. The problem is when I do a vlookup, it pulls the first match, and if that match is blank - that's not what I want. I need the first match that has a number in it. Cell G33 - Here is where...
  10. M

    Find and replace first non-zero value in a range

    Hi All, I have the data shown below... I'd like to find the first non-zero value in a row (highlighted yellow, in this case always a '1') and replace this value with a zero. Suggestions appreciated! Thanks in advance Matt PS This is seabird ringing/banding data. The first value is the year of...
  11. P

    Codes & maths

    Hi Mrexcel Greats & Eric W. Am wondering how to convert a long column with all manner of numbers to a non-zero digit: eg 210.66876468 to 2.1066876468 0.0486788676 to 4.86788676 11.694324507 to 1.1694324507 0.2516033212 to 2.516033212 Thanks
  12. S

    Equation to retrieve the average of the last three non-zero entries in a range

    I would like to get an cell equation that provides the average of the last three non-zero entries in a range. Given the data Value Desired Result 1 0 0 2 3 2 0 2 4 3 0 3 5 4 6 5 7 6 8 7 0 7 9 8 0 8 where cell A1="Value" and "A15"=9 etc. Starting with row 6, the average of the last three...
  13. E

    Adding last non-zero value in a column

    I'm having problems setting up a formula. This is what I have so far: =IF(AND(ISBLANK(E19),ISBLANK(F19)),0,INDEX(H10:H100,MAX((ABS(H10:H100)>0)*ROW(H10:H100))-(CELL("Row",H10)-1))+F19-E19) I have an 'if' function, so if two cells are blank, then a 0 is returned in the total column. Later if...
  14. P

    Return Header if specified row contains a value

    I have a data set that looks like this: A B C D E F P 2 0 2 0 0 3 Q 0 3 1 1 1 0 R 6 0 0 0 1 0 S 0 5 0 3 0 0 T 0 0 5 0 0 2 U 0 0 7 0 0 0 I need to be able to search that table by putting selecting an input value from P-Q then if that selected row has a value greater than 0. I need it to...
  15. B

    Lookup - Last non Zero Value

    This is the formula that im currently using but I'm hoping someone can help me modify it. =LEFT(LOOKUP(2,1/(Raw!$A$2:$A$5000&Raw!$B$2:$B$5000=$A8&I$6),Raw!$L$2:$L$5000)) This lookup works great, but im looking for it to find the last non-zero value. For example my table looks something like...
  16. S

    Get average of non-zero cells

    Hi, I have 6 cells into which any number (including zero) can be entered. What I want is the average of these 6 cells where the entered value is >0. ie, if the values entered are - 38,40,0,0,0,0 - into each cells, then the average must be 39, which is the average of non-zero cells. How can this...
  17. L

    Automatically fill a cell based only on a non-zero value in another cell

    Thanks in advance for any help or advice. I'm not sure how to include an Excel fragment in the post so I'll do it the old fashioned way. I have 4 columns. In column A are calendar dates. In column B are values of either 0, 1 or 2. These values are manually inserted by the user. What I...
  18. P

    Finding the minimum value that is not zero for each sub-set of data

    Hi - I have a data set that is sorted by Member ID and each Member ID can have multiple transactions. In my desired outcome I only want the non-zero minimum value displayed for the group next to the corresponding transaction in each subset of Member ID's. See example below: <tbody> Unique ID...
  19. V

    Find LATEST non-zero value in a row

    Hello, I am trying to figure out how to display the latest non-zero value in a row. Note, I am not looking for the last value (there is plenty of help here and else where on that) but the latest/most recent. Here is an example: <tbody> <tbody> Source Data </tbody> <tbody> 19.5...
  20. S

    Finding the First Non Zero number in a list

    Is there a way to pick the first non zero number in a list? The data is in a dynamic range that is updated each time a program is run, and I need to find the first non zero number in the list. Also, the data cannot be sorted to use the MATCH function as the numbers are specific to a date that is...

Some videos you may like

This Week's Hot Topics

Top