|
Average of the minimum+50 in a row
I have rows f figurs
How does I creat a function where I want the average of the minimun+50. I use an array where I have 200 columns an 7000 rows. I want a function which invastigates row by row for minimum and then calculate the average of the cells, in the row, which contain figures min+50. Kind Regards PEA |
Not quite sure, but here is a shot at what I think that you mean
=AVERAGE(IF(A20:I20=MIN(A20:I20)+50,A20:I20)) as an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "PEA" wrote in message ... I have rows f figurs How does I creat a function where I want the average of the minimun+50. I use an array where I have 200 columns an 7000 rows. I want a function which invastigates row by row for minimum and then calculate the average of the cells, in the row, which contain figures min+50. Kind Regards PEA |
He bob
It will not work, but what I mean is, in the calculation the average I do not want to use cells where the cell value is 50 higher than the minimun value ex 1001 1003 1002 1003 1080 Her I do not want to use 1080 in the average regards "Bob Phillips" wrote: Not quite sure, but here is a shot at what I think that you mean =AVERAGE(IF(A20:I20=MIN(A20:I20)+50,A20:I20)) as an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "PEA" wrote in message ... I have rows f figurs How does I creat a function where I want the average of the minimun+50. I use an array where I have 200 columns an 7000 rows. I want a function which invastigates row by row for minimum and then calculate the average of the cells, in the row, which contain figures min+50. Kind Regards PEA |
Okay,
here is an alternative. I have also added another test to ignore blanks =AVERAGE(IF((A20:I20<=MIN(A20:I20)+50)*(A20:I20<" "),A20:I20)) still an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "PEA" wrote in message ... He bob It will not work, but what I mean is, in the calculation the average I do not want to use cells where the cell value is 50 higher than the minimun value ex 1001 1003 1002 1003 1080 Her I do not want to use 1080 in the average regards "Bob Phillips" wrote: Not quite sure, but here is a shot at what I think that you mean =AVERAGE(IF(A20:I20=MIN(A20:I20)+50,A20:I20)) as an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "PEA" wrote in message ... I have rows f figurs How does I creat a function where I want the average of the minimun+50. I use an array where I have 200 columns an 7000 rows. I want a function which invastigates row by row for minimum and then calculate the average of the cells, in the row, which contain figures min+50. Kind Regards PEA |
| All times are GMT +1. The time now is 04:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com