| Users Online Now: 1,362 (Who's On?) | Visitors Today: 271,451 | |
| Pageviews Today: 361,654 | Threads Today: 105 | Posts Today: 1,634 |
| 03:06 AM | ||
| Users Online Now: 1,362 (Who's On?) | Visitors Today: 271,451 | |
| Pageviews Today: 361,654 | Threads Today: 105 | Posts Today: 1,634 |
| 03:06 AM | ||
Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative | |
| Anonymous Coward User ID: 72561339  United States08/03/2016 04:02 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:03 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Been trying to figure this out with the help of google but no luck: Quoting: Anonymous Coward 72561339 I'm trying to find out the number of positive numbers from a row containing both positive & negative numbers & with that info want to get the avg positives from the whole list. I tried to extract the positives/negatives by "data -> filter by number" but it is not working correctly (still leaves a negative in the range) example: If a row having 10 positive numbers and 100 negative numbers I want to have a cell reflect "10" or ".10" Anyone know? thank you very much for trying to help me oops in the example should be if 10 positives and 90 negatives then "10" or ".10" for 10% |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:10 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:12 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:13 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative I would make another column that holds a one if its a positive number Quoting: Anonymous Coward 1712129 (abs(x1)+x1) / (2*x1)) then sum that column I don't understand. Thank you for helping. I would think there should be a direct formula but if not I can do something else you put that formula into another column. that formula will put a 1 if its positive and a 0 if its negative. then you can just sum up that column. |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:17 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative I would make another column that holds a one if its a positive number Quoting: Anonymous Coward 1712129 (abs(x1)+x1) / (2*x1)) then sum that column I don't understand. Thank you for helping. I would think there should be a direct formula but if not I can do something else you put that formula into another column. that formula will put a 1 if its positive and a 0 if its negative. then you can just sum up that column. what does abs represent? what is "+x1"? I want to sum not for the median number but percentage positive of the whole. Perhaps using average is the wrong term. Thank you |
| Anonymous Coward User ID: 44534076 United States08/03/2016 04:17 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward User ID: 72714759  Spain08/03/2016 04:17 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:19 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative I would make another column that holds a one if its a positive number Quoting: Anonymous Coward 1712129 (abs(x1)+x1) / (2*x1)) then sum that column I don't understand. Thank you for helping. I would think there should be a direct formula but if not I can do something else you put that formula into another column. that formula will put a 1 if its positive and a 0 if its negative. then you can just sum up that column. what does abs represent? what is "+x1"? I want to sum not for the median number but percentage positive of the whole. Perhaps using average is the wrong term. Thank you abs is absolute value x1 is the column/row of the current cell your working with. |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:21 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:21 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:24 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:24 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:25 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! this does not count 0 as a positive. |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:29 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! this does not count 0 as a positive. that's fine for the specific thing I am doing. I'm trying to understand your way but not understanding the formula. where in the formula do you enter the cell number? |
| Anonymous Coward User ID: 6305916 United States08/03/2016 04:29 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:30 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! this does not count 0 as a positive. that's fine for the specific thing I am doing. I'm trying to understand your way but not understanding the formula. where in the formula do you enter the cell number? x1 is the cell |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:31 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! the con about this I see is =SUM(E14:E17)/4 where 4 represents the number of rows that I must enter manually each time. I wonder if there is a formula which can count the Number of rows into the average (denominator)? |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:31 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:34 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! the con about this I see is =SUM(E14:E17)/4 where 4 represents the number of rows that I must enter manually each time. I wonder if there is a formula which can count the Number of rows into the average (denominator)? there is an average function its what your looking for. |
| Anonymous Coward User ID: 41415651  Canada08/03/2016 04:35 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:36 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! the con about this I see is =SUM(E14:E17)/4 where 4 represents the number of rows that I must enter manually each time. I wonder if there is a formula which can count the Number of rows into the average (denominator)? there is an average function its what your looking for. omg can't believe that didn't click lol yes! |
| Anonymous Coward User ID: 1712129 United States08/03/2016 04:38 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Add a row with formulas, which give back the number above its cell if its positive and zero if its negative. Then you could average the sum of the positive numbers. Quoting: Anonymous Coward 72714759 Add another row with formulas which give back 1 if the value above is positive and zero if its negative. The sum in that row would give you the number of positive values awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! this does not count 0 as a positive. that's fine for the specific thing I am doing. I'm trying to understand your way but not understanding the formula. where in the formula do you enter the cell number? I do thing a bit different than most my peers. "IF Statements" are slow so I go with direct calculations when possible. |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:38 PM Report Abusive Post Report Copyright Violation | |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 04:40 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative ... Quoting: Anonymous Coward 72561339 awesome that works!! thank you! =IF(G15>0,1,0) then just average 1's among rows of 1's and 0's thank you everyone! this does not count 0 as a positive. that's fine for the specific thing I am doing. I'm trying to understand your way but not understanding the formula. where in the formula do you enter the cell number? I do thing a bit different than most my peers. "IF Statements" are slow so I go with direct calculations when possible. I am intrigued by you abs function I just can't figure how that one works as I cannot enter cell reference everytime it says "x", will have to look into it another time. |
Ziconic  User ID: 72584978 United States08/03/2016 05:02 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Highlight row, then click data tab, sort, options, sort left to right, ok. Then sort by row (whatever number), values, smallest to largest (or opposite if you need.) Now that positive and negatives are separated, it's easy to define a range in the average function. “Luck is what happens when preparation meets opportunity.” ~Seneca You can lead the Democrats to knowledge, but you can't make them think. |
| Anonymous Coward User ID: 72720187 United States08/03/2016 05:05 PM Report Abusive Post Report Copyright Violation | |
| Ziconic User ID: 72584978 United States08/03/2016 05:12 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Yeah, that's where I went with it too. However, OP said the numbers are in a row, hence the extra steps needed above. “Luck is what happens when preparation meets opportunity.” ~Seneca You can lead the Democrats to knowledge, but you can't make them think. |
| Anonymous Coward User ID: 72720187 United States08/03/2016 05:31 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative |
| Ziconic User ID: 72584978 United States08/03/2016 05:41 PM Report Abusive Post Report Copyright Violation | Re: Help with Excel formula - trying to find avg of positive numbers from a row which includes Negative Yeah, that's where I went with it too. However, OP said the numbers are in a row, hence the extra steps needed above. aaahh F the row Type in a cell = AVG then hold ctl and select all positive numbers to define field. You could do it that way too. If it's a long row with hundreds of numbers though, clicking each cell could get a little tedious, not to mention ripe for omission errors. We will need to hear from OP on how long that row is. “Luck is what happens when preparation meets opportunity.” ~Seneca You can lead the Democrats to knowledge, but you can't make them think. |
| Anonymous Coward (OP) User ID: 72561339 United States08/03/2016 06:43 PM Report Abusive Post Report Copyright Violation | |
