|
Help Please with If statement / vlookup
How do I write an if statement if I only want to get the averages of the
first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
Donna,
The easiest way to handle this is with a User Defined Function (UDF). The following will work for you. Function AvgFirstFiveVals(ParamArray args() As Variant) As Double '11th March 2010 'Author: Peter Atherton Application.Volatile Dim i As Variant, _ tmpRange As Range, _ cell As Range, _ tmpSum As Double, _ tmpCount As Integer, _ iLim As Integer iLim = 5 For i = 0 To UBound(args) If Not IsMissing(args(i)) Then Set tmpRange = Intersect(args(i).Parent.UsedRange, args(i)) For Each cell In tmpRange If IsNumeric(cell) And cell < 0 And _ Len(cell) < 0 Then tmpCount = tmpCount + 1 tmpSum = tmpSum + cell AvgFirstFiveVals = tmpSum / tmpCount If tmpCount = iLim Then Exit Function End If Next cell End If Next i End Function This has to be copied in to the Visual basic Editor before it can be used. Press ALT + F11, Insert, Module then paste the code in the Module. Press ALT + Q to quit the VBE and return to the spreadsheet. Enter the function as you would for a SUM e.g. =AVGFIRSTFIVEVALS(A1,C34,Sheet2!A67...Sheet3!B34:B 40) You can also link it to another workbook if you like. HTH Peter "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
I didn't understand the part regarding the "other sheet" - however in order
to calculate the first 5 non empty values in range A1:A10 you can use the following Array-Formula: {=AVERAGE(SMALL(A1:A10,ROW(1:5)))} *** The formula is to be confirmed with CTRL+SHIFT+ENTER rather than with simply ENTER. The curly brackets {} are not to be typed manually, those are entered by the Excel, when the formula is entered as an Array formula. Micky "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
=AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24)
Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
=AVERAGE(SMALL(K9:K10,ROW($1:$5)))
How do I add a range of cells from another sheet in the workbook the works great, but I need to look at a range in another sheet along with the range in this sheet "מיכאל (מיקי) אבידן" wrote: I didn't understand the part regarding the "other sheet" - however in order to calculate the first 5 non empty values in range A1:A10 you can use the following Array-Formula: {=AVERAGE(SMALL(A1:A10,ROW(1:5)))} *** The formula is to be confirmed with CTRL+SHIFT+ENTER rather than with simply ENTER. The curly brackets {} are not to be typed manually, those are entered by the Excel, when the formula is entered as an Array formula. Micky "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
Donna,
Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
Billy, I don't understand the insert an +NA() in the data
{=AVERAGE(SMALL(K7:K20,ROW($1:$5)))} This formulal works great to find an average for the first five cells that have a value, however If want to look at cells from two different sheets how would I write the formula. I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get a #value! ( one of the sheets is Mar and one is Feb) ***Basically I need to get an average from the first five cells that have values, But I need to look at cells in two different sheets. "Billy Liddel" wrote: Donna, Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
I was replying to you regarding the UDF. There is an Excel function called NA
that produces the #N/A! value. I entered this into the test data '=NA()',to produce a #VALUE! error like the one you describe. The UDF would also produce a value error if there was a division by zero. If the data is numeric with no errors it will give the average Perhaps Micky can help you with the other solution. "Donna" wrote: Billy, I don't understand the insert an +NA() in the data {=AVERAGE(SMALL(K7:K20,ROW($1:$5)))} This formulal works great to find an average for the first five cells that have a value, however If want to look at cells from two different sheets how would I write the formula. I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get a #value! ( one of the sheets is Mar and one is Feb) ***Basically I need to get an average from the first five cells that have values, But I need to look at cells in two different sheets. "Billy Liddel" wrote: Donna, Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
Help Please with If statement / vlookup
Hi Billy, I am sorry, I am trying both things at once.
there isn't a error in any of the data, but some of the cells are blank... "Billy Liddel" wrote: I was replying to you regarding the UDF. There is an Excel function called NA that produces the #N/A! value. I entered this into the test data '=NA()',to produce a #VALUE! error like the one you describe. The UDF would also produce a value error if there was a division by zero. If the data is numeric with no errors it will give the average Perhaps Micky can help you with the other solution. "Donna" wrote: Billy, I don't understand the insert an +NA() in the data {=AVERAGE(SMALL(K7:K20,ROW($1:$5)))} This formulal works great to find an average for the first five cells that have a value, however If want to look at cells from two different sheets how would I write the formula. I tried this, {=AVERAGE(SMALL((K6:K19,Feb!K19:K24),ROW($1:$5)))} but I get a #value! ( one of the sheets is Mar and one is Feb) ***Basically I need to get an average from the first five cells that have values, But I need to look at cells in two different sheets. "Billy Liddel" wrote: Donna, Is therre an error in the data? I insert an +NA() into the data and this produced a #VALUE! error. Correct this and it will work. HTH Peter "Donna" wrote: =AVGFIRSTFIVEVALS(K6:K7,Feb!K16:K24) Billy, I added the UDF to the sheet and this my formula but I am getting a #value! in the cell I have two sheets, the other sheet is "Feb"... "Donna" wrote: How do I write an if statement if I only want to get the averages of the first five cells with a value? I will need to look in about 10 cells, but only avg the FIRST 5 with a value. Some will be blank and Some will be on a different sheet |
| All times are GMT +1. The time now is 02:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com