# AverageIFS statement in VBA and question with dropbox selection

#### konficus

##### New Member
Hi guys,

I need a correction for my formula, I want to calculate averages for given dates,

Code:
``````    Dim FirstDate As Date
Dim LastDate As Date

FirstDate= Application.InputBox("First Date")
LastDate = Application.InputBox("Last Date")

Range(Cells(3, anay), Cells(3, anay)).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,>=" & FirstDate& ",All!B:B,<=" & LastDate & ",All!A:A,A3),"")"``````

gives error.

and ccording to top question is it possible to not giving first and last date with manual, can be choosen from a dropbox,for example,

months from 4 to 10, 4 = April, 10 = October,
If choose 5 from drop box, first date will become 01/05/2017 last date become 31/05/2017?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### konficus

##### New Member
any help is much appreciated

#### FormR

##### MrExcel MVP
Hi, the formula I think should be:

Code:
``Cells(3, anay).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"``

To only have to enter a month number you could try something like:

Code:
``````Dim lMonth As Long
Dim FirstDate As Date
Dim LastDate As Date

lMonth = Application.InputBox("Enter Month Number 1 to 12")
FirstDate = DateSerial(Year(Date), lMonth, 1)
LastDate = DateSerial(Year(Date), lMonth + 1, 0)

Cells(3, anay).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"``````

To have the month number be selected from a combo box you'll need to create a userform.

#### konficus

##### New Member
Dear FormR,

thank you, that is super, and possible a little additional, it is working first date month and last date month but it took this year, but now i am analysing 2018, is it possible to make year 17 or 18 like year selection?

#### FormR

##### MrExcel MVP

is it possible to make year 17 or 18 like year selection?

Hi, something like this?

Code:
``````Dim lMonth As Long
Dim lYear As Long
Dim FirstDate As Date
Dim LastDate As Date

lMonth = Application.InputBox("Enter Month Number 1 to 12")
lYear = Application.InputBox("Enter the Year")

FirstDate = DateSerial(lYear, lMonth, 1)
LastDate = DateSerial(lYear, lMonth + 1, 0)

Cells(3, 3).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"``````

#### konficus

##### New Member
Thanks worked. Perfect.

#### konficus

##### New Member

And i need help again, how can i give a variable for averageifs formula,

what i want, in my loop i want to change " All!I:I " part according to excel.

Cells(3, 3).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"

It will become I:I then M:M then Q:Q ( everytime same amount of colown will skip...

any ideas?

thank you so much.

#### FormR

##### MrExcel MVP
what i want, in my loop i want to change " All!I:I " part according to excel.

Hi, can you show us your loop code?

#### konficus

##### New Member
actually didnt create a loop code yet, first iwould like to figure out how to change colowns,

below code is makes same formula for each cells for example from 1 to 5...

Dim yilne As Long yilne = MsgBox(ActiveSheet.Name & " Analiz Yılı 2018 mi?", vbYesNo)
If yilne = vbYes Then
yilne = 2018
Else
yilne = Application.InputBox(prompt:=ActiveSheet.Name & " Analiz Yılı Nedir?", Type:=1)
End If

rt = 3
hc = 2
sonay = 7
kacay = cnt
'kacay = Application.InputBox(prompt:=ActiveSheet.Name & " KaçAy var?", Type:=1)

For ilk = 1 To kacay

Dim lay As Long
Dim ilktarih As Date
Dim sontarih As Date

lay = Cells(rt - 1, hc)
'lay = Application.InputBox("Enter Month Number 1 to 12")

ilktarih = DateSerial(yilne, lay, 1)
sontarih = DateSerial(yilne, lay + 1, 0)

Cells(rt, hc).Select
Cells(rt, hc).Formula = "=IFERROR(AVERAGEIFS(All!g:g,All!\$B:\$B,"">=" & ilktarih & """,All!\$B:\$B, ""<=" & sontarih & """,All!\$A:\$A,\$A3),"""")"

hc = hc + 1

Next ilk

#### konficus

##### New Member
I guess,i found it with a little help, i solved it via index formula, my code become;

op = 1
basla = 2

Do Until Cells(1, basla) = ""

For ilk = 1 To kacay

Dim lay As Long
Dim ilktarih As Date
Dim sontarih As Date

lay = Cells(rt - 1, hc)
'lay = Application.InputBox("Enter Month Number 1 to 12")

ilktarih = DateSerial(yilne, lay, 1)
sontarih = DateSerial(yilne, lay + 1, 0)

Cells(rt, hc).Select

Cells(rt, hc).Formula = "=IFERROR(AVERAGEIFS(INDEX(All!G:AA,0," & op & "),All!\$B:\$B,"">=" & ilktarih & """,All!\$B:\$B, ""<=" & sontarih & """,All!\$A:\$A,\$A3),"""")"

hc = hc + 1

Next ilk
basla = basla + kacay
op = op + 4
Loop

Replies
0
Views
393
Replies
3
Views
74
Replies
1
Views
460
Replies
12
Views
1K
Replies
5
Views
287

1,129,754
Messages
5,638,170
Members
417,011
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back