AverageIFS statement in VBA and question with dropbox selection

konficus

New Member
Joined
Dec 14, 2015
Messages
21
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?

thank you for your helps.
 

Excel Facts

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

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,498
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 14, 2015
Messages
21
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
Joined
Aug 18, 2011
Messages
6,498
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 14, 2015
Messages
21

ADVERTISEMENT

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.
 

konficus

New Member
Joined
Dec 14, 2015
Messages
21
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
Joined
Dec 14, 2015
Messages
21
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
 

Watch MrExcel Video

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top